October 5, 2010 at 6:46 am
Has anyone managed to use the ‘Copy database’ facility via SQL Management Objects in order to copy a 2008 database to an instance that is running SQL2005?
It always fails on me at the last stage for no obvious reason. The service accounts are the same for both servers, so it cannot be a security issue (both on the same domain).
As source server I have used both SQL2008 (build 10.0.2531) and SQL2008R2 (build 10.50.1734).
As destination server I have used SQL2005 build 9.0.4053 as well as 9.0.4035.
Towards the end of the SSIS package execution I always get the following error in the Application log of the destination server:
Event Name: OnError
Message: ERROR : errorCode=0 description=Warning: The SQL Server client assembly 'microsoft.sqlserver.types, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Any ideas welcome!
Cheers,
JohnA
MCM: SQL2008
October 5, 2010 at 7:30 am
From memory you can only use the database copy wizard to go up versions or between instances of the same version.
Recall that a SQL Server 2008 database cannot be re attached to a SQL Server 2005 database server, based on this, if using the detach and attach method inside the copy database wizard you will definitely get a failure!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2010 at 7:45 am
I wouldn't try it that. Instead, script out the 2008 database to a script or set of scripts. Then you can run those on the 2005 instance, fixing what you need to in order to make them work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2010 at 7:59 am
Grant Fritchey (10/5/2010)
script out the 2008 database to a script or set of scripts. Then you can run those on the 2005 instance, fixing what you need to in order to make them work.
Probably the only and certainly the most sensible way of backtracking the database!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2010 at 12:23 pm
Grant Fritchey (10/5/2010)
I wouldn't try it that. Instead, script out the 2008 database to a script or set of scripts. Then you can run those on the 2005 instance, fixing what you need to in order to make them work.
I've already tried that:hehe: (script with data SSMS option)
2 Gb db --> 17Gb script 1.5 hours later:-D
Can't even load such a beast of a .sql file onto Mgt Studio, it fails miserably...
Cheers,
JohnA
MCM: SQL2008
October 5, 2010 at 1:55 pm
John__A (10/5/2010)
Grant Fritchey (10/5/2010)
I wouldn't try it that. Instead, script out the 2008 database to a script or set of scripts. Then you can run those on the 2005 instance, fixing what you need to in order to make them work.I've already tried that:hehe: (script with data SSMS option)
2 Gb db --> 17Gb script 1.5 hours later:-D
Can't even load such a beast of a .sql file onto Mgt Studio, it fails miserably...
You're scripting the data too then.
Seperate them out. Script the objects. Use sqlcmd to export the data to flat files. Move stuff over that way.
There is no easy way to downgrade databases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2010 at 1:58 pm
Or script out the schema, recreate, and then to a data flow task for every table (you may need to drop all foreign keys, insert data, then recreate them).
October 6, 2010 at 5:39 am
Derrick Smith (10/5/2010)
Or script out the schema, recreate, and then to a data flow task for every table (you may need to drop all foreign keys, insert data, then recreate them).
Yeah true, an SSIS job to move the data after moving the structure would be a good idea too. Probably even a better idea.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2010 at 6:04 am
Thanks guys, some food for thought there.:-)
Looks like what appeared as a simple 'copy' operation on the vendor's manual is likely to end up as a complicated set of tasks...
Cheers,
JohnA
MCM: SQL2008
October 6, 2010 at 7:51 am
John__A (10/6/2010)
a simple 'copy' operation
it is if you are copying instance to instance where versions are supported!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply