Rajendra Gupta
Attach a SQL Database in SSMS

Move a SQL database using the detach and attach method

January 29, 2020 by

This article explores the process of moving a SQL database using the detach and attach method.

Introduction

Sometimes we require moving data files (primary, secondary) and log files to different drives and servers. Some of the cases where you might require moving database files are as follows:

  • Lack of free space: Suppose you have a disk having less free space. You might move a few files from one drive to another so that you can resolve space issues and database files can grow
  • Database Management: Sometimes, we require segregating data files and log files into separate drives
  • Database movement: We might require moving a database to separate server, and in this case, you do not require a database on the source instance
  • Database upgrade: We can also upgrade a database to a higher version by moving the data and log files to a higher version instance

There are multiple ways to move a database such as backup & restore, take the database offline. In this article, we will discuss the detach and attach method to move a SQL database.

Move a database using detach and attach method

We can use the following steps for moving a database using detach ad attach method.

Collecting SQL Database information

Planning is necessary before we move a database. As you know, a SQL database might contain multiple data files(one primary and multiple secondary) and transaction log files. We should collect the information about existing files, their locations before start moving a database.

  • Run the sp_helpfile command in current database security context and save information separately:

    SQL Database information

  • We can also use sys.database_files catalog view and fetch the required information:

    sys.database_files catalog view

    In the above screenshots, we see that our database has single data and log file.

  • Run the sp_helpdb command with a specific database, also returns the information:

    Output of sp_helpdb command

  • Using the GUI method, right-click on the database and properties. In the files section, you can also view files information:

    GUI method

In the above screenshots, note that database files are in the default locations. Suppose we want to move these files to ‘C:\sqlshack\Demo’ folder.

Detach a SQL database

In this method, we first detach the database from the source instance. Right-click on the desired database in SSMS and click on Tasks->Detach:

Detach a SQL database

It opens the detach database window, as shown below:

 Detach SQL database

Here, we get the following information’s:

  • Database name
  • Drop connections: We should have any existing connections to the database else SQL Server cannot detach it. Also should look at the message column in the image below, a database has 6 active connections

If you click on the active connections message, it gives prompt that you should close the existing connections:

Active connections message

You can review and close the connections manually. We also have a checkbox drop connections to drop all connections for you before detaching the SQL database.

  • Update Statistics: We can use this option for refreshing statistics before detaching the database. Ideally, you should update statistics, especially if you are moving the database to a different SQL instance
  • Status: Currently, it shows status not ready due to active connections.

Let’s put a tick on both Drop Connections and Update Statistics before detaching this database as shown below:

Drop Connections and Update Statistics

Instead of detaching the database, it is better to generate a script using this wizard and execute the script. This way, you can also learn the equivalent script as well. Under the Script drop-down box, choose the Script Action to New Query Window command:

The similar script as it is shown below will be generated:

The first script puts the database into single-user mode by rollback existing running queries:

Note: You should verify the AUTO_UPDATE_STATISTICS_ASYNC setting as well. If it is enabled, you cannot access the database into single-user mode.

The second script uses the sp_detach_db stored procedure in the master database. You can see the parameter @skipchecks = false in this query. In the detach database window, we put a check on Update Statistics. This option specifies whether SQL Server to update statistics or not before detaching the database.

  • @Skipchecks false: Update statistics true
  • @Skipchecks true: Update statistics false

Execute the script, and in the output, it puts the database into single-user mode, performs update statistics and detaches the database:

Output of detach SQL database

Move SQL Database files into the desired location

Now, move the database files from the old location to the new location. It is recommended to use copy-paste instead of cut paste. Copy-paste gives you the flexibility to quickly rollback in case of any unforeseen circumstances.

Attach SQL Database files from the new location

Once we have copied the database files into new locations on the desired SQL instance, connect to new instance, right-click on the Databases folder and from the context menu, choose the Attach command:

Attach a SQL Database in SSMS

It opens the Attach Databases window, as shown below:

Attach SQL database window

Click the Add button and navigate to the location of the database files, select the MDF file, as shown below:

browse to the directory having the database files

Click OK. SSMS automatically fills the associated files and their location if it is in the same directory. You should validate the path of all database files and modify if required:

validate the path of all database files

By default, it attaches the database with the original database name. We can modify the value for column attach as in the attached database window.

As I always recommend, you should generate a script so that you can learn the equivalent script as well. It generates the following script.

It uses a create database script with the FOR ATTACH option to attach a database from existing data and log files.

Execute this script, and you can see the database in Object Explorer. You can verify the database new location:

database in the object explorer

Limitation of Attach and detach method

  • We cannot detach system databases: master, model, msdb and tempdb
  • We cannot use it for a replicated, mirrored or a database with the snapshot
  • A suspect database cannot be moved
  • You cannot attach a database to a lower version of SQL Server. For example, we cannot attach a SQL 2016 database in SQL 2014 instance
  • If we attach a database having a higher version, SQL Server maintains the database compatibility. We can change the compatibility level once the database is online

Conclusion

In this article, we explored moving a SQL database using Detach and Attach method. You should review all available options to move a database and choose the appropriate option that suits your requirement.

Rajendra Gupta
Maintenance, Server management

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

19,523 Views