how to migrate a 2005 SQL DB to a new 2005 SQL Server

RAFF-
RAFF- used Ask the Experts™
on
I need to move a SQL database to a second SQL server. Both servers are under utilized and I would like to consolidate them. There is only one db on Server A and 3 db’s on server B, so obviously, I’m looking to move the db on Server A to Server B. I would like to provide minimal downtime and I’m not sure if there is a way to do that by mirroring or just simply backing up the db and moving it over to Server B. I would also like to make it as transparent as possible to the users. Your advise is greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Make sure both servers are updated to the same service pack level and hotfix (not totally necessary but recommended).  Simply do a detatch and an attach:

sp_detach_db @dbname= 'database_name'

Copy the files Data files are databasename.MDF, databasename.LDF to the new server then run:

sp_attach_db
@dbname= 'database_name',
@filename1='Drive:\FullPathTo\databasename.MDF', @filename2='Drive:\FullPathTo\databasename.LDF'
If you have Litespeed tool, you could take full back of desired db and restore it wherever you want.
Commented:
Keep in mind that when you move the database it is not going to be completely transparent to your users.  The trick is to not only make the downtime as minimal as possible, but to set appropriate expectations with your users.  Moving the database is the smallest portion of the battle in most cases.  Whatever was using that database on Server A is going to have to have it's connections updated to point to server B.  In addition you're going to need to know exactly what server logins have permissions to that database so that you can make sure the server logins exist on server B.  You can move the database a whole lot of different ways... Some of the most popular methods:  

Place the database in admin or single user mode, take a full backup and restore it to Server B
Detach the database, move the files across the network to server B and reattach them.
Implement database mirroring from server A to Server B and when they are fully in synch "fail over" to server B.
Implement transaction log shipping from Server A to Server B and when they are fully in synch "fail over" to server B.

Whichever method you choose, make sure you engage your users to let them know what is going to happen.  If the database is used by a 3rd party application, it wouldn't hurt to engage the software manufacturer as well to make sure you know how to appropriately update any connections.  Whatever you do, if you want it to be as transparent as possible, plan ahead and make sure you cover your bases with your users.

Author

Commented:
Thank you Swindle for your help. I will be disusing our options with the users accessing this db.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial