At some point, you will need to migrate your existing databases to a new server and/or instance. There are two ways of accomplishing this. The first way is backing up the database and then restoring the database onto the new server. The second way is detaching the database and attaching it to the new server.
In parts one and two of this series, we covered the backup and restore process. For a production environment, it may make the most sense unless there is a good downtime window after hours for detaching and attaching the database to the new server. For instance, if downtime is very limited and you must migrate the database as soon as possible prior to any extended maintenance windows AND the database must be fully functional during the process, then it would be best to perform a FULL database backup on the source server followed by a restore on the destination server. You would then point your applications to the new server followed by restoring a DIFFERENTIAL backup from the source server to the new server to capture any new information that had been added to the database prior to the cutover.
However, while detaching and attaching a database induces downtime since the database will be completely inaccessible, the downtime is limited almost solely to file copy time between the source and destination servers. Also, you don't have to worry about restoring a backup (including a possible differential). Thus, if you have a long maintenance window, know how large your database is, and can determine with some reasonable accuracy the length of time it will take to transfer the database MDF and LDF files, this option becomes appealing.
Let's explore how to do this in SQL Server Management Studio.
First, launch SSMS and expand the Databases hierarchy. Right-click on the database you wish to detach and then navigate to "Tasks" followed by "Detach..."
Once you have done this, the following window will open asking you if you want to drop all active connections (turning the database into single-user mode and disconnecting all users) and update statistics (updates optimization statistics in the database that might be out-of-date). If you don't check the box to drop connections and there are still users connected then the database will not detach. Thus, be careful here. Make sure that users are aware that they must not be utilizing the database whatsoever at the time you are detaching the database.
Of course as you do this you will notice the progress circle spin.
Once complete, you can view the database tree again and your database will no longer appear as an option. This is a successful detach.
The next step is simple: copy the MDF and LDF (log) files over to the new location.
After this is done, simply follow the steps to attach the database by right-clicking the database hierarchy and clicking "Attach..."
A window will pop up prompting you to add a MDF file to attach to the instance. See the following screenshot for an example when the window opens and you click "Add":
The next screenshot you will see ALL of the files associated with the database you are attaching once you have selected the appropriate MDF file.
Click "OK" and wait. Eventually the window will close and your database will show up in the databases tree. If it doesn't, refresh the tree.
Congratulations! You have just detached, migrated, and attached your database to the new server!
Important: detach/attach is not a substitute for backing up your database. It is vital to perform regular backup operations on your databases. See "Part Two" of this series for more information here.