Detaching/Attaching System and User Databases

So I am moving from a physical server to a VM MS SQL Server 2005 and am planning to detach and attach the databases to the new server. Here are my questions. :-)

Here is the setup:

Existing: MS Server 2003 32-Bit, MSSQL Server 2005 SP3,
New: MS Server 2008 64-Bit, MSSQL Server 2005 SP3,

Should I detach system databases before detaching all of the user databases?
Should I attach the system databases before or after all of the user databases?
Should I export the login's using this information before transferring any databases?

Thank you in advance for all of your help!

asked Apr 28 '10 at 09:59 AM in Default

Ron 3
97 ● 5 ● 5 ● 6

(comments are locked)

1 answer:

Personally I wouldnt bother about moving system databases, I just use the vanilla ones created on installation. I like to create stuff as its needed on the new server so that anything that isnt required gets left behind - a bit like Spring Cleaning I guess. It may be because I dont have enough controls in place to ensure unused objects get deleted and things get kept too long. You may in a better state than me and want to copy all settings from the server and so moving the system databases would make sense for you.

I also prefer to use Backup/Restore to move a database to a new instance rather than detach/attach. Either is a valid way to move it and by and large its preference over any particular functionality or requirement. To my mind its easier to leave the old server version online until the new version is working just in case its needed as a fall back rather than have to re-attach it first ...

answered Apr 28 '10 at 10:16 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

+1 What he said. I'd love to add something, but can't.

Apr 28 '10 at 10:23 AM Grant Fritchey ♦♦

@FatherJack...Well said.

Apr 28 '10 at 10:30 AM DaniSQL

I Forgot to mention that I don't have any MS SQL experience. I didn't know that I could get away with not transferring the system databases. That is very nice information!!! I am going to have to take another look at the backup/restore method.

--- Does it matter that I will be transferring all the databases to a server with the exact same server name?
--- Should I restrict the user databases to single user mode before backing up to make sure that the data isn't being changed?

I originally thought that I would do backup/restore, but went with the attach/detach with no reasoning...

Apr 28 '10 at 10:42 AM Ron 3

OK, working with a server with the same name could make this a whole new question! You could run into some network confusion issues. As far as SQL is concerned it should be fine so long as you dont need to connect one to the other. I tend to use the offline status for the old server (right click the database in SSMS and choose Tasks | Take offline) to prevent any data changes while the move is in progress.

Apr 28 '10 at 10:49 AM Fatherjack ♦♦

You have been very helpful Father Jack. Thank you for sharing your knowledge with the myself and the community.

Apr 28 '10 at 10:56 AM Ron 3

(comments are locked)