How do I attach a database in SQL Server?

Some common questions relating to attaching databases in SQL Server:

• What does it mean to attach or detach a database?

• How do I detach a database?

• How do I attach a database?

• What does it mean to attach and rebuild the log?

• How do I do it in SQL Server Express?

• When might I consider detaching and attaching?

• Are there any risks or warnings?

• What about attaching between versions and editions of SQL Server? (Standard to Enterprise? 2000 to 2008? 2012 to 2008?)

What Is Detach or Attach and How do They Work?

We'll start with detach. When you detach a database in SQL Server, you are taking the database offline and removing it from the SQL Server instance from which you are detaching it. The databases data and log files remain in tact and are left in a consistent state so you can then attach the database at a later point or to another SQL Server instance. Attach connects the data and log files from a database that has been properly detached (or that were copied from a cleanly shut down instance of SQL Server) to an instance of SQL Server and brings the database online.

How Do I Detach a Database?

You can do this in T-SQL or from the SQL Server Management Studio GUI.

In the GUI, you right click on the database you wish to detach, select All Tasks and click on Detach. From there you'll get the detach dialog. You can choose to drop connections first to forcibly disconnect any active connections and rollback work they were in the middle of executing. You can also choose to update statistics before the detach.

In T-SQL:

-- You don't want to be in the database you are trying to detach
USE Master
GO

-- Optional step to drop all active connections and roll back their work
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Perform the detach
EXEC sp_detach_db 'DatabaseName'
GO

For the system stored procedure sp_detach_db there are two paramaters that you can pass in optionally:

• @skipchecks - acceptable input is 'True' or 'False' if 'True', SQL Server will update statistics before detach. If 'False', it won't. If you don't specify anything here the statistics will be updated in SQL Server 2005 or later.

• @keepfulltextindexfile - The default here is 'True' - if this is set to true, the full text index metadata will not be dropped during the detach.

To see a lot more about detach and some more details on the risks I highlight below, the Books Online article for sp_detach_db is a good place to start.

How Do I Attach a Database?

You can also do this in T-SQL or from the SQL Server Management Studio GUI.

(NOTE: If you have the data and log files from a database that was not properly detached, your attach may not work. When detach occurs, the database is brought offline and the log and data files are put into a consistent state. This also happens when a service is cleanly shut down.)

In the GUI, you right click on the top level Databases folder for your instance and select Attach. In the next dialog you would then select the primary data file (.MDF) of the database you wish to attach and ensure you have the other files selected and their appropriate locations specified, and click OK, attaching your database.

In T-SQL the best way to do this in SQL Server 2005 and forward is through the CREATE DATABASE command. This is the method that is supported beyond SQL Server 2012. If you want to see how to use sp_attach_db, you can see that in the books online articles for [sp_attach_db][3] or [sp_attach_single_file_db][4]

When you have your log file and data files available and they are consistent this is the T-SQL approach:

-- Using Create Database and the FOR ATTACH clause to attach

CREATE DATABASE DatabaseName 
    ON (FILENAME = 'FilePath\FileName.mdf'), -- Main Data File .mdf
    (FILENAME = 'FilePath\LogFileName.ldf'), -- Log file .ldf
     (FILENAME = 'FilePath\SecondaryDataFile.ndf)  -- Optional - any secondary data files
    FOR ATTACH 
GO

You can see more about the Create Database statement in books online as well.

How Do I Detach/Attach in SQL Server Express?

It's actually the same. If you are using SQL Server Management Studio Express you can use the detach/attach dialog in the GUI described above or the T-SQL steps through SSMS Express described above as well. No difference with Express there.

If you don't have SSMS Express, you can download it (Here is the SQL Server 2012 Express version).

Of you can enter into a SQLCMD session and use the same T-SQL constructs described above.

When Should I Consider Doing a Detach or Attach?

First a word on what detach and attach is not meant to be used for: Backup and Recovery Detach and Attach is not a way to backup your database for routine recovery purposes. There are no transaction log backups this way, it puts your database into a state where the database files can be deleted accidentally and is not a good way at all for this purpose.

That said, detach and attach are good for a few use cases (not exhaustive, feel free to edit to add or create a new answer with more):

• Sometimes for migrations (although I prefer backup/restore for those as discussed in my answer here)

• When you want to remove a database that is no longer actively used but have the ability to attach later as needed.

• In certain troubleshooting situations, this may be called upon

• Don't have the space to backup or to restore both a data and log files to another

StackExchange Database Administrators Q#30440, answer score: 33