Move User Database file to another drive

Have you ever received an email like below and then got a big pain in your stomach?  Disk E: is running out of room and you need to move one of your user database files before you totally fill the disk.  Yes, your VM admin might be able to add disk space but they are out sick or this is a physical machine and you need to order bigger disks and you need to make this change right now.

First I query the database and see what drives my Adventureworks database is sitting on.

select name,physical_name,state_desc from sys.master_files 
where database_id=db_id('AdventureWorksDW2012')

It is sitting on the E: drive and I am able to take this database offline without affecting any users.

 

To take a user database offline, I issue the following command.

alter database [AdventureWorksDW2012] set offline

 

After that completes, I can now copy the file to a new destination.  I usually copy the file instead of cutting and pasting just in case the server blue screens or the copy gets interrupted some how.  This way you still have the original file in case of an issue and you can retry.  After the entire process is done and you are able to bring the database back online, you may delete the file from the original location.

Here, I copied the file to the directory c:\temp\temp.  Not the best of locations, but it is only for a day and on a production server, you would copy it to a standard Data directory.

 

 

Now you need to tell the database you moved the file to a new location.  Below I use the alter database command to accomplish that.  Under the filename parameter, you are providing the new location c:\temp\temp.  You can also rename the file as long you rename the physical file name also.

alter database adventureworksdw2012 modify file(name=AdventureWorksDW2012_Data, 
filename='c:\temp\temp\AdventureWorksDW2012_Data.mdf')

 

 

Now we need to bring the database online, by using the alter database command again.

alter database [AdventureWorksDW2012] set online

Whoops, we receive an error saying: Operating system error 5:  “Access is Denied”

This happened because we copied the file to a new location that does not currently have any other database files in it.  We will need to provide Sql Server with the appropriate operating system permissions to have access.

 

Right click the c:\temp\temp directory in file explorer and click Properties.

Click the Security tab and click Edit and then Add.

You will need to add the following username and then click ok.

NT SERVICE\MSSQLSERVER

 

You need to make sure that the MSSQLSERVER user has full control access, see below.

 

After the permission is added, we attempt to bring the database back online, by using the alter database command.

alter database [AdventureWorksDW2012] set online

Yayyy…This time we are successful since the appropriate rights have been granted to the MSSQLSERVER user.

 

We can query the database to make sure that our change actually took place.

select name,physical_name,state_desc from sys.master_files 
where database_id=db_id('AdventureWorksDW2012')

Below, you can see the file now is located in the c:\temp\temp directory.

After this step, you are free to delete the original file that was located on the E: drive.

These steps only work on user databases (not Master, MSDB, Tempdb, Model), to move those requires different steps and will be a future article.

Moving a database file can be stressful the first few times you need to do it.  Just follow the steps above and practice on your development or test servers and you will have confidence in no time.