Contents

How to detach a database in SQL Server

This topic describes how to detach a database using dbForge Studio for SQL Server. The detached files remain and can be reattached by using CREATE DATABASE with the FOR ATTACH option. The files can be moved to another server and attached there.

Detach a database

Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can be later used to attach the database to any instance of SQL Server, including the server from which the database was detached. However, there are several restrictions while detaching a database.

You cannot detach a database in the following cases:

• The database is replicated and published. If replicated, the database must be unpublished. Before you can detach it, you must disable publishing.
• A database snapshot exists on the database. Before you can detach the database, you must drop all of its snapshots.
• The database is being mirrored in a database mirroring session. The database cannot be detached unless the session is terminated.
• The database is suspect. A suspect database cannot be detached; before you can detach it, you must put it into the emergency mode.
• The database is a system database.

To detach a database:

1. Use Database Explorer to connect to the instance of the SQL Server database engine and then expand the instance.

2. Select the name of the database you want to detach.

3. Right-click the database name and then click Detach Database. The Detach Database dialog box appears. You can detach several databases at once. For this, in Database Explorer select several databases.

4. Optionally, you can disconnect connections to the specified database. Note, you cannot detach a database with active connections.

5. Optionally, you can update the existing optimization statistics. Note, by default, the detach operation retains any out-of-date optimization statistics when detaching the database.

6. Click OK.

7. If a failure occurs while detaching a database, you can read the error message. To do this, hover the mouse cursor to an error in the Status field.

Download dbForge Studio for SQL Server and try it absolutely free for 30 days!

If you want to learn how to detach and attach a database in SQL Server, feel free to watch this video.

Want to Find out More?

• Previous
• Next