How do you move a database from one server to the other using SQL Server Management Studio?

Question

I need to move a database, but I want to make sure I do it safely and with all triggers, stored procedures and functions.

I was think to simply detach the database from my server using SQL Server Management Studio and move the .mdf and .ldf files to the new directory and attach it to the new server.

Can someone guide me please?

Answer 1

If you use "Detach/Attach" function you'll be able to transfert the database with all triggers and objects that are inside the DB. You must know that only the database will be tranfert. If you need to have all the logins for the old server, you can use this link from Microsoft. After you run the procedure, you can have some orphaned users. If you read carefully the Miscrosoft KB, you will find a link to solve this issue.

If you tranfer from 2005 to 2008 you must use this link to tranfer logins.

Hope this help you.

Answer 2

Too late to the party .. Since this question was edited and showed up on the front page ... My answer below will help future visitors ..

I was think to simply detach the database from my server using SQL Server Management Studio and move the .mdf and .ldf files to the new directory and attach it to the new server.

Detach / Attach is not a good practice to move database from one server to another. If something goes wrong and you cant attach the database, you will be out of luck.

Best is to use backup/restore.

If you have very less downtime, you can use logshipping or mirroring and then take final tail backup (incase of logshipping) or have mirroring run in sync mode and then failover. You can migrate the logins, jobs, SSIS packages, etc once you failover (or you can prep them before hand).

If you have a planned downtime window, just use start-migration that migrates everything from one server to anther e.g. databases, logins, linked server, jobs, etc