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:

  1 2 3

  Use SQLShack go Exec sp_helpfile

  

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

  1 2 3 4 5 6

  SELECT file_id,        type_desc,        name,        physical_name,        state_desc FROM sys.database_files;

  

  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:

  1	sp_helpdb 'sqlshack'

  

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

  

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:

It opens the detach database window, as shown below:

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:

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:

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:

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:

It opens the Attach Databases window, as shown below:

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

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:

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:

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.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023
• Learn the PostgreSQL COALESCE command - January 19, 2023