Move Datafiles in SQL Server – Part 1

Move Datafiles in SQL Server – Part 1
3.3 (66.67%) 6 votes

Introduction

There are a number of situations which would warrant the movement of database files or transaction log files from one volume to another on the same server. These may include:

  1. The need to format the volume assuming it was not formatted properly when SQL Server was installed. Recall that when installing SQL Server, it is recommended that 64K allocation unit size is used to format the volumes. If this is not done at the point of installation and needs to be done later, it will obviously require preserving a backup of the database fist or creating a new, properly formatted volume and moving the database to this new volume.
  2. The need to use a new volume assuming the limits have been reached for the underlying storage. A good example would be the 2TB limit of a VMware Data Store. This is the case as of VSphere 5.0. Higher versions of VSphere have much higher limits.
  3. The need to improve performance by managing IO. One more reason you may want to move datafiles is performance. There are cases where a database is created with multiple datafiles all sitting on one disk until it becomes obvious, as the database grows, that the you have created a “hot region” in the storage layer. One solution would be creating new data files and rebuilding clustered indexes, another would be moving data files.

Scenario One: Moving User Databases

The steps involved in moving a user database involve the following:

  1. Take the database offline
  2. Update the system catalog with the new location
  3. Copy the datafile physically to the new location
  4. Bring the database online

Listing 1 shows the commands executed to achieve these steps.

Listing 1 Moving Datafiles

It is important to note that when taking a database offline the number of active sessions may delay the process. Scheduling a downtime to perform this task would be a good idea. During such a downtime, the application owner should stop application services connecting to the database before the DBA attempts to take the database offline. There are cases where it is not so convenient to take the database offline, then shutting down the instance would be the best option. In such a case the approach would be slightly different:

  1. Update the system catalog with the new location
  2.  Shut down the instance
  3. Copy the desired datafile physically to the new location
  4. Startup the instance

In both approaches, the concept is same: it involves updating the system catalog in the master database and then physically relocating the desired datafile. In both case, the datafile must be cleanly closed. Let’s take a look at the steps involved in the first approach.

Fig. 1 Verify the Location of Datafiles
Fig. 1 Verify the Location of Datafiles

The first step would be to check the state of thing to start with. The proceed to set database offline and modify the system catalog.

Fig. 2 Set Database Offline and Modify Catalog
Fig. 2 Set Database Offline and Modify Catalog

As seen in Fig. 3, once we update the catalog, querying sys.master_files tells us the new location the master database expects the datafile to be in whether or not we have physically moved the file. In Fig. 4, we also see that it is not possible to bring the database online without first moving the file to the new location physically (and rename the file to match the new name specified in the catalog).

Fig. 3 New File Locations
Fig. 3 New File Locations
Fig. 4 Missing File
Fig. 4 Missing File

We also would like to point out that once we copy the file, we lose the previous permissions on the file and SQL Server will not be able to open the file when we try to bring the database online. We must edit the file permissions and add grant the account NT SERVICE\MSSQLSERVER full permissions on the file.

Fig. 5 Copy the Datafile
Fig. 5 Copy the Datafile
Fig. 6 Permissions at Destination
Fig. 6 Permissions at Destination
Fig. 7a Permissions at Source
Fig. 7a Permissions at Source
Fig. 7b Permissions at Source
Fig. 7b Permissions at Source

If we were to attempt to bring the database online again with these permissions missing, we will get an error 0x5 (Access Denied). If we were to do something like moving the datafile using an agent job, we find that the SQL Server Agent account acquires ownership of the file and we can bring the database only because the SQL Server Agent account is the same as the SQL Server Account.

Fig. 8 Access Denied on New Datafile
Fig. 8 Access Denied on New Datafile

Assuming you were trying to bring the database online using the SSMS GUI, you would see these errors in Event Viewer as well as in the SQL Server error log if you look closely. In addition, if you were using the second approach (restarting the entire instance), you would observe that the database would be stuck at the recovery stage. Examining the Error Log would tell you what is really going on.

Listing 2 Moving Datafiles Using an Agent Job

Fig. 9 Permissions on Datafile When Using Agent Job
Fig. 9 Permissions on Datafile When Using Agent Job
Fig. 10 Database Online
Fig. 10 Database Online

Automating the Process

Just for fun we can decide to use SQL Server Agent Job for the entire process. We configure a job step for each step of our process. This may be useful if you want to be a superstar DBA and schedule such a migration overnight while you go on home and relax with family. Definitely you would want to ensure you configure a notification to fire when the job succeeds so you are sure it actually does get done while you are away.

Listing 3 Performing the Task Using an Agent Job

Conclusion

In this article, we have seen one way to move user database files in SQL Server. We have also seen the need to make sure we give attention to the permissions on the datafile at the new location so that we do not encounter errors when bringing the database back online. We have also seen that we can put all these in a SQL Server Agent job using the T-SQL and PowerShell Subsystems. In a subsequent article, we shall see two other methods of moving database files to a new volume.

Kenneth Igiri

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