How to detach or attach a database to SQL Server

There are times when you need to Attach or Detach a SQL Database from the server in Sql Server so that you can move it or back up purposes and then have it re-attached later. There are two way in which you can do these, one is via the SQL Management Studio application or the other way is through SQL itself.

Detaching Database

Via SQL Management Studio

In the Management Studio, right click on the database that you wish to detach and in the context menu, select Tasks and then Detach. When the pop-up appears, click on the two check boxes beside the name of the database. Unless there is a particular reason, its best to click on both check boxes then click ok.

• Drop Connections - so all applications that are currently connected will loose immediate connectivity to the dabase.
• Update Statistics

There will be a short pause whilst the database is detached and then you can do with the database whatever you need to do to it.

Via SQL

Its very simple with SQL, you just call the sp_detach_db procedure with one mandatory parameter and two optionals.

• Name - name fo the databse as it appears in the list of connected databases, this is a compulsory field.
• Skipchecks - if the value is true then Update Statistics is not carried out, otherwise, logically it will do.
• KeepFullTextIndexFile = if the value is not given or is true then the full text index file will be kept (true) or dropped (false).

e.g.

exec sp_attach_db 'Exampledb',true;

Attaching a Database

You don't have to attach the database to the same server that you originally detached it from, it can be attached to another server.

Via SQL Management Studio

In the database explorer, right click on the Databases node and select "Attach" and then a new window will appear. Click on Add and a file selector should appear with a list of the databases in the current folder. The database files should ideally be in this folder but if its not, don't worry. Using the folder explorer, locate the dabase .MDF file you wish to attach.

Once you have selected the database you wish to add, the top window will disappear and in the previous window, the database files will appear in the second list box. With everything being ok, click on OK and then the window will close and the database has been attached.

Via SQL

The SQL to attach a database is simple, you just use sp_attach_db with the following parameters and then run.

• dbname - Name of the database to appear in the database explorer.
• Filename1 - Full filename + path of the .mdf file that is being attached.
• Filename2 - Full filename + path of the .ldf file that is being attached

e.g.

sp_attach_db @dbName = N'ExampleDB',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Example.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Example.ldf';

Tags - SQL

Last Modified : June 2023