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.
-- Listing 1 - Identify the Location of datafiles select db_name(database_id) as [Database Name] , name as [Logical File Name] , type_desc as [File Type] ,physical_name as [Physical File Name] from sys.master_files where db_name(database_id)=N'WideWorldImporters'; go
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.
-- Listing 2 - Detach a Database from an SQL Server Instance USE [master] GO ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO GO USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'WideWorldImporters' GO
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)
-- Listing 3 - Attach a Database to an SQL Server Instance USE [master] GO CREATE DATABASE [WideWorldImporters] ON ( FILENAME = N'G:\MSSQL\Data\WWI_Primary.mdf' ), ( FILENAME = N'G:\MSSQL\Data\WWI_UserData.ndf' ) LOG ON ( FILENAME = N'F:\MSSQL\Log\WWI_Log.ldf' ) FOR ATTACH GO
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.
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.
-- Listing 4 - Backup a Database backup database [WideWorldImporters] to disk ='WideWorldImporters_01.bak', disk ='WideWorldImporters_02.bak', disk ='WideWorldImporters_03.bak', disk ='WideWorldImporters_04.bak' with stats=10 -- Listing 5 - Verify the files contained in the backup set restore filelistonly from disk ='WideWorldImporters_01.bak'; restore headeronly from disk ='WideWorldImporters_01.bak'; restore verifyonly from disk ='WideWorldImporters_01.bak' with loadhistory; restore verifyonly from disk ='WideWorldImporters_01.bak', disk ='WideWorldImporters_02.bak', disk ='WideWorldImporters_03.bak', disk ='WideWorldImporters_04.bak' with loadhistory; -- Verify details of the backup set at source use msdb go select bus.backup_set_id, bmf.physical_device_name from backupset bus inner join backupmediafamily bmf on bus.media_set_id=bmf.media_set_id where backup_start_date >= getdate() -1
-- Listing 6 - Verify the files contained in the backup set -- drop database [WideWorldImporters]; restore database [WideWorldImporters] from disk ='WideWorldImporters_01.bak', disk ='WideWorldImporters_02.bak', disk ='WideWorldImporters_03.bak', disk ='WideWorldImporters_04.bak' with move 'WWI_Log' to 'G:\MSSQL\Log\WWI_Log.ldf', stats = 10
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.
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.
- Deploying a Certificate for Encrypted Connection SQL Server - September 16, 2020
- How to Configure Database Mail in SQL Server - September 14, 2020
- Configuring AlwaysOn Availability Groups on SQL Server - September 3, 2020