Using Detach and Attach to Transfer Your Data

If you want to make sure that you know what data has been transferred and you are able to take your database offline for a period of time, using Detach and Attach to transfer data might be the right option for you. The major disadvantage of using the Detach/Attach method is that you have to make your database unavailable for a short period of time while it is detached. You need to make a copy of the .mdf file (mdf stands for master database file) and that can only be done while the database is detached. This means that your database will be unavailable as long as it takes you to make a copy of the database. If you have a large database, you may not be able to make the copy in a reasonable amount of time.

Tip

If you choose the Detach/Attach option, it may be to your advantage to investigate replication or high-speed copy options available via third parties.

If your business runs during a known set of hours with a specific period of down time, then Detach/Attach is more likely to be the best option for you. Once your business day is complete, you can detach the database, copy it to the desired location, attach it to the target server, and reattach it to the source server. Let's walk through this process.

Detaching a Database

1.	From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Connect to the source server in Object Explorer.
2.	Right-click the database you want to transfer and click Tasks | Detach in the context menu. This will open the Detach Database dialog box:
3.	Select the Drop Connections checkbox and then click the OK button. This will detach the database, making the .mdf and .ldf files available to be moved or copied to the target server location. Copy the .mdf or .ldf files for your database to the target server now.

Tip

Here is the T-SQL statement for detaching a database:

EXEC sp_detach_db AdventureWorks GO

Once you have detached your database, you can copy it to the target server. In order to bring the source and the target databases online, you will need to re-attach the original database on the source server and the copied database on the target server. To do so, follow the next set of steps in both locations.

Attaching a Database

1.	In SQL Server Management Studio, right-click the Databases folder and select Attach from the context menu. This will open the Attach Database dialog box:
2.	Click the Add button and then select the file or files to attach. Note If you use multiple files for your database structure, make sure all of the files are represented in the second list in the dialog box after you have added the .mdf file to the first list. Tip If you want to attach the copied database files to the source server, which would result in two initially identical databases on the same server, you can change the name of the database as you are attaching it. All you need to do is supply a different name in the Attach As column in the first listbox.
3.	Click the OK button and you have successfully attached the database. Tip Here is the T-SQL statement for attaching a database: EXEC sp_attach_db @dbname = N'AdventureWorks', @filename1 = N'd:\MSSQL\AdventureWorks.mdf', @filename2 = N'd:\MSSQL\AdventureWorks_Log.ldf' GO

If you choose to use Detach/Attach to transfer data, make sure to reattach the database you originally detached. If you do not do this, the applications based on this database location will not function as expected if at all.