Copying Databases from one Instance to Another – II
Posted by decipherinfosys on August 21, 2008
In one of our previous blog posts, we had covered the different methods available to a Developer/DBA to copy/move the user databases from one instance to another in SQL Server. You can read that post over here. In that post, we had also covered the different things to look out for when performing the copy/move from one instance to another. In this post, we will cover the very first method – Attach and Detach for the user databases.
The steps involved are these:
1) Detach the user database(s) from the source instance.
2) Copy over the files from the source machine to the target machine. If using the same machine but different instances, you can copy over the files to the location that you want on the same machine.
3) Attach the user database(s) to the destination instance.
4) Collect 100% statistics on the user database(s).
For #1, detaching is a very simple and straight forward process. You need to first make sure that you kill the connections to the database in question (read this post to understand why) and then being in the master database, detach the database in question using the sp_detach_db system stored procedure.
We had blogged about the attach and detach process before and you can access that post here. However, in that post, we had not covered attaching a single file database which sometimes becomes a need when you get a file from someone else. At times, you might just get a data file and you are required to attach it to an instance. You can do that by using the system stored procedure: sp_attach_single_file_db. The syntax of that command is pretty much self explanatory and you can look that up in BOL. When that command is run, it automatically creates a transaction log file automatically upon a successful attach process of the data file.