Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Detaching and Attaching a Database

By Brian Knight,

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.

Total article views: 16570 | Views in the last 30 days: 9
 
Related Articles
ARTICLE

Moving Database Files Detach/Attach or ALTER DATABASE?

Learn why ALTER DATABASE should be preferred over Detach/Attach for moving database files on the sam...

FORUM

detach and attach

how can we detach and attach system database

SCRIPT

Get Detach or Attach all user databases script

Script to generate script to detach or attach user databases

FORUM

Detach and Attach database move filegroup

Detach and Attach database move filegroup (What will happen to the prior transactions)

FORUM

Detach a remote database from a server

detaching databases remotely

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones