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.

move0

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.

move1

 

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

alter database [AdventureWorksDW2012] set offline

move2

 

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.

move3

 

 

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')

 

move4

 

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”

move5

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

move6

 

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

move7

 

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

alter database [AdventureWorksDW2012] set online

move8

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.

move9

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s