Detaching and attaching a database is an advanced trick that can be useful in anything

from transporting your database to recovering from a disaster. It can also help you rollback from a service pack if needed.

This article will guide you through the uses of detaching and attaching a database and how you can

do this trick yourself.

Before we discuss how to detach and attach a database, let's talk about why you would do this. This procedure

allows you to detach a database from one server and attach it on a different server or even on the same server.

Before you can attach the server, you must make sure that the destination server has the same sort order, code page and unicode collation or the database will not attach.

So you may be wondering why you would ever want to do this. Some

of the uses of this would be:

• Moving a database from one physical disk to another. For example, if a database has grown past

  the disk's capacity, then you could use this procedure to move it to a new drive.

• Transporting the database to a client location or a new home. Detaching and attaching

  a database is much faster than backing up a database and restoring it to another server, which was the traditional method of moving databases.

• Upgrading a MSDE database to SQL Server.

• In the event of a disaster and the master database must be rebuilt.

The last bullet above is the most important benefit. Imagine the scenario where your SQL Server crashes and you can't get the server operational due to a corrupt master.

After you have exhausted every other method of getting your master database up (documented in a previous article), you may have to rebuild the master.

The best method to recover from this would be to recover the last known good backup.

As a last resort, you could bring up your SQL Server by taking the following action:

• Rebuild the master to the same sort order, code page and collation.

• Attach the databases

Do not reinstall SQL Server in this scenario. You risk deleting your old physical files which you will use to attach.

To detach a database you can run the sp_detachdb system stored procedure. This stored procedure will remove all entries of the database in the master database and the file will then be portable.

In other words, don't try this on a production database unless you're in a crisis. You could at that point burn an image of the database and log files onto a CD and distribute it. This is not the best way to distribute a database

if you're doing mass distribution however.

sp_detach_db
@dbname = 'Northwind',
@skipchecks='false'

When the @skipchecks parameter is set to true, UPDATE STATISTICS won't automatically be run before the database is detached.

Detaching a database is optional. Another method which I have tested but do not recommend is to shut down SQL Server, copy the database and log files over to a separate directory, then start SQL Server back up. We stop SQL Server to

avoid a sharing violation. After SQL Server is started, you can delete the database if needed and attach it back or move it from the file in the new directory.

Attaching the database can be done with the following syntax:

sp_attach_db
@dbname = 'Northwind',
@filename1='C:\MSSQL7\DATA\Northwind_Data.MDF',
@filename2='C:\MSSQL7\DATA\Northwind_Log.LDF'

Now that you have attached the database, if you are running replication, you will want to run sp_removedbreplication to remove replication.

After your database is attached, back it up and run dbcc checkdb to ensure your database is fine.

The detach and attach feature has been introduced in SQL Server 7.0. Keep in mind that you can't migrate a database through this method from one version of SQL Server to another at this time.

These procedures can save you from some sticky situations when restoring a database through the attach method. DMO also has fully exposed these methods as well.

You can read about more SQL Server inner workings in Inside SQL Server.