Change SQL server database file location without taking the database offline

Changing SQL server database file location is not a frequent practice, but what happens when you require to change file location to different directory? To avoid any database interruption DBCC SHRINKFILE command with the EMPTYFILE option, will allow to perform this migration operation while the database is online.

Step01:

First check the database files size and available space

SELECT DB_NAME() AS DbName,

name AS FileName,

size/128.0 AS CurrentSizeMB,

size/128.0-CAST(FILEPROPERTY (name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files;

Step02:

Go to database property you intend to move location, add file with new Path.

Property – files – add

Capture0001

Step03:

Now use DBCC SHRINKFILE command with the EMPTYFILE option to migrate the data from the old file (TEST_Data) to the new one (TEST_NEW_Data01) as an online operation. The old data file is empty now.

DBCC Shrinkfile (TEST_Data, EMPTYFILE)

Step04:

Now check the database file size and free space, to ensure old data file become empty.

SELECT DB_NAME() AS DbName,

name AS FileName,

size/128.0 AS CurrentSizeMB,

size/128.0-CAST(FILEPROPERTY (name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files;

Step05:

Now you can remove the old data file or stop the auto growth, so that all upcoming data will write to new data file.

Capture0002

All that steps will be done online and no require to bring database offline or reboot.

 

BY: MD ULLAH

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