This article explores the process of moving a SQL database using the detach and attach method.
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:123Use SQLShackgoExec sp_helpfile
We can also use sys.database_files catalog view and fetch the required information:123456SELECT file_id,type_desc,name,physical_name,state_descFROM 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:1sp_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:
ALTER DATABASE [SQLShack] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'SQLShack', @skipchecks = 'false'
The first script puts the database into single-user mode by rollback existing running queries:
ALTER DATABASE [SQLShack] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
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.
CREATE DATABASE [SQLShack] ON
( FILENAME = N'C:\sqlshack\Demo\SQLShack.mdf' ),
( FILENAME = N'C:\sqlshack\Demo\SQLShack_log.ldf' )
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
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.