Move Datafiles in SQL Server – Part 2

Total: 12 Average: 4


In the first part of the two-part series, we explored migrating databases by first updating the master database system catalogs which contain records of the physical location of data files. In the current article, we shall look at two other methods of migrating databases in SQL Server which essentially have the same effect through the approach is different.

Scenario Two: Detach and Attach

The Detach and Attach method of migrating databases is based on the architecture of SQL Server – one instance to many databases architecture. Databases can be moved from one instance to another while we keep in mind that Server level objects on which these databases depend are moved using a separate process. Such server level objects may include logins, jobs, Linked Servers, alerts, and operators etc.

The Detach and Attach method can be implemented either using Transact-SQL or using the GUI. In this article, we shall focus on the Transact-SQL approach. When you detach a database from an instance using this method, all records of the database existence on the instance are removed from the master database system catalog. The only surviving records are backup history records stored in msdb and these cannot tell you the original location of the physical files.
The key difference between a detach and an actual database drop is that a detach operation will first check for dirty pages and open transactions. A detach operation will not succeed if there are open transactions of dirty pages. It is also worth noting that a database participating in replication cannot be detached. This implies that before detaching a database, you must be aware of the current location of the physical files since the next step would be to physical copy the files to a new location. Listing 1 shows us how to get this information from the sys.master_files system catalog.

Move Datafiles in SQL Server
Fig. 1 Check Database File Locations

The standard stored procedures used to accomplish a Detach and Attach Procedure are master.dbo.sp_detach_db and master.dbo.sp_attach_db. Listing 2 shows the syntax for running the stored procedure master.dbo.sp_detach_db. Also noticed we have explicitly disconnected all users from the database to ensure no open transactions.

Move Datafiles in SQL Server
Fig. 2 Database Detached

Our database movement would likely be to another SQL Server instance entirely. It may also be that we wish to move some of the files to another disk (recall in Part 1 we outlined the possible reasons for moving database files). In this example, we will move the transaction log file from drive G to drive F as shown in Fig. 3. Once this is done we can attach the database using the code in Listing 3. The error shown in Fig. 4 occurs because the user running the Transact-SQL does not have permission to manipulate the transaction log file. We can easily remedy this by granting full control of the file at the operating system level to the user running the stored procedure master.dbo.sp_attach_db. Note that when you use the GUI, this user would be the SQL Server service account. (See Fig. 5)

Move Datafiles in SQL Server
Fig. 3 Move the Logfile to a New Location
Move Datafiles in SQL Server
Fig. 4 Permissions Error
Move Datafiles in SQL Server
Fig. 5 Granting Permissions

Once this permission issue is sorted, we run the statement in Listing 3 again followed by that in Listing 1. Listing 1 confirms to us that the transaction log file has been relocated.

Move Datafiles in SQL Server
Fig. 6 New File Location

Scenario Three: Backup and Restore (With MOVE)

The third method of relocating database files we shall highlight is a simple backup and restore with the MOVE option. We have particularly found this useful when dealing with VLDBs. The downtime associated with performing a backup and restore can be significantly smaller than that associated with copying a large data file across disks. There is also the risk of corrupting the datafile if something goes wrong during the copy.

We demonstrate this approach by taking a backup of the WideWorldImporters database (Listing 4), dropping the database and then restoring the database with the move option. (Listing 6). In a live scenario, it may be necessary to investigate the files contained in the backup set using the filelistonly restore option or even go further to check the completeness or otherwise of the backup set at source by examining certain tables in the msdb database. Restore verifyonly will return an error indicating the expected number of media families in a case where it is not possible to examine the source msdb database.

Move Datafiles in SQL Server
Fig. 7 Incomplete Media Set

Once all checks are done we can proceed with our objective of restoring the database and relocating the file we want to relocate using the move option as shown in Listing 6. Once again, we can verify that we have achieved this by repeating the query in Listing 1.

Fig. 8 Transaction Log File Relocated
Fig. 8 Transaction Log File Relocated
Move Datafiles in SQL Server
Fig. 9 WideWorldImporters Database


In this article, we have explored two additional ways of moving database files from one drive to another using Transact-SQL and native SQL Server tools. These methods are applicable to both data files and transaction log files. We have also mentioned the preferred method of moving large databases would be doing a backup and restore with the move option. However, in a case where the task involves moving the database to another server entirely and your environment rides on a SAN, unmounting volumes and mounting them on the destination server would work just great and would definitely be much faster than a backup and restore. This implies detaching the database from the source instance before unmounting the volumes from source and then attaching the database to the new instance after mounting the volumes at the destination.

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri

Latest posts by Kenneth Igiri (see all)