Moving mdf and ldf files to another other SQL server

[ashar99]

I just want to move one database off of an SQL Server that is no longer needed and move it to a running SQL Server. Can I just stop both SQL Servers, move the mdf and ldf files from one server to the other and then restart the server ? And would there be anything else to do on the database side ? I know I'll have to change the client connection parameters.

 

[TomKane]

Hi,

What we do is that we go the source server and take a back of the database out to file

Then create a blank database of the same name on the target server - we then restore the backed up datebase to the blank one (using a force restore) and that sorts us out.

Hope this helps,
Tom

 

[ashar99]

Thank you

 

[SQLBill]

ashar99,

Your method will also work. I usually backup my database before I detach, move the .mdf/.ldf and attach them. The backup is just in case something goes wrong with the move.

-SQLBill

 

[mb22]

Suppose you DETACH the database from the current SQL Server (..then do what you need to do ... say copy the mdf/ldf to another SQL Server ... fine!). Now when you ATTACH the database back to your CURRENT SQL Server later to resume normal operations .. do you have to reapply all the user ids that had access to the database prior to detaching?

Within the database itself what about the permissions that those user ids ..say had to the different tables or stored procedures in the database .... Do you have to recreate all that again?

 

[SQLBill]

You shouldn't. Logins and permissions are kept in the Master database.

-SQLBill