Systems Engineering and RDBMS

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).

5) Resolve the issues pertaining to the orphaned users (and other issues that we had mentioned in our previous post pertaining to the dependencies on the master and the msdb databases etc.).

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: