August 27, 2009 at 5:22 pm
I need to transfer our SQL 2000 to a new machine, and need to transfer the databases. I've tried the copy tables and objects DTS which works fine, but neither seem to copy the "default values" within each table. I've also tried the the Transfer Database Task, but that just comes up with a "unknown error" when executed. I've trawled the web for ideas but seem to be getting nowhere, if it wasn't for the shear quantity of tables I could have entered them manually again. Thanks for any help in this. S.
August 28, 2009 at 6:29 am
Why not detach and attach on the new server?
August 28, 2009 at 8:08 am
Thanks for your response. Sorry how does one detach the database? other than through EM. I'm basically trying to physically copy a whole database, including views, properties, indexes, contents etc from one machine to another. S.
August 28, 2009 at 11:07 am
You can do this in EM or via T-SQL
BOL: http://msdn.microsoft.com/en-us/library/ms190794.aspx
Articles: http://www.sqlservercentral.com/articles/Administration/attachanddetachagain/656/
The one thing you need to be aware of is that it doesn't move logins. It moves users, but they'll be orphaned on the new server.
You can move logins first with sp_help_revlogin[/url] (from MS). This will save the passwords.
Or you can move them later with the sp_change_users_login procedure, which will match up orphaned users with logins, or create new logins.
September 2, 2009 at 1:07 am
That worked a treat, many thanks.
September 2, 2009 at 1:11 am
That worked a treat, many thanks.
September 25, 2009 at 3:12 pm
I can easily copy defaults property by making use of copy sql server objects task in dts.
http://msdn.microsoft.com/en-us/library/aa933510(SQL.80).aspx
MJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply