SQL Server Database(s) shifting strategy !

[miq]

hi,
I need to move all databases and the sql server structure to a new location on a different machine.

This is what i think i need to do to make this transition a smooth one.


1. detach/offline all databases
2. Backup all databases (Full Backups)
3. Backup master, msdb and model databases
4. Move all user and system databases to new machine
5. After installing sql server on that machine. Restore
master then msdb then model database (well, I haven't
changed any setting from model database so, i think
i am not required to restore model database)
6. Restore all user databases
7. attach them all


Is their anything wrong with this ? Or, I need to add more
steps, or alter few or all of them.


Bye
miq

 

[lemosley01]

You can't do a full backup of a database after detaching it. You would need to do the backup of the database first, then detach it.
Once detached, the database is no longer available and all you can do with it is backup that actual files the comprise the database.

Rest of that seems fine, but why detach the databases? Why not just backup the databases and restore them on the target server? That way if something does go wrong (bad backups or something), the other server is still ready to go, and you don't have to go through reattaching everything.

 

[miq]

hie,
Nice suggestion. Going through past posts I came across another method which is but copy the database files
.MDF and .LDF and paste them to the destination server and have those files attached. It looks simple, but what about system databases? Are they of any concern here?

Well, the proper method seems to be of backup/restore but detach/attach is also an option.

What do you think?


bye
miq