Last week, I wrote a post on how to relocate Oracle data files from one drive to another. Today, let us see how simple it is to do the same in SQL Server.
I recently had to move 125 databases on an instance to a new and expanded volume mountpoint to allow for growth, which I was able to do in no time using this method. I am running SQL 2014 but it should not be different in your version.
Also, please checkout my post on “Attach/Detach Gotchas” which is very related to this method but is just that – a warning list!
Space got tight on a drive and I knew that there was space on another drive. I had already set this particular database with multiple secondary file groups/files (.ndf files) instead of a huge and single .mdf file (which would have made the whole thing a lot harder).
The steps at a high level:
The procedure to relocate data files is extremely simple
- Notify users that you need to do maintenance on the database
- Check the space situation on your host
- Identify the files that can be moved using [YourDB].sys.database_files or Get-DbaDatabaseFile from dbatools
- Save the commands to OFFLINE/ONLINE + other just-in-case items
- ALTER DATABASE MODIFY to relocate file pointers in the system database
- ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
- Physically move the files to a new drive where there is adequate space
- Verify and ALTER DATABASE [YourDB] SET ONLINE;
Let us go over each step in a detailed fashion
1. Notify users that you need to do maintenance on the database
This step needs no explanation. However, if you do not have a meta-data database with owner information, the best place to start is to notify the users that are currently connected. You can simply use sp_who2 or sp_whoisactive.
You could use PowerShell to notify users who are connected. This takes the hard-work out of identifying whose login is what and finding their email ID’s.
I used the below snippet to notify (using the function referenced above)
Send-MailToConnectedUsers `
-ServerInstance 'MySQLInstance' `
-From 'MyID@MyCompany.com' `
-Subject 'MySQLInstance: SQL Server maintenance' `
-Body 'Please note that maintenance is scheduled at 20:00 EST on SQL Server instance MySQLInstance. You are receiving this email since you are connected to the instance at this time. The maintenance will be completed in about 15 minutes. Please save your work and logoff as everyone will be disconnected for this operation. Thank you for your patience and understanding.'
2. Check the space situation on your host
I use the Get-DBADiskSpace PowerShell function that is part of DBATools. It gives me a nice output of all the drives/volumes with free-space information (something like below)
PS C:\> Get-DbaDiskSpace -ComputerName MYSQLHOST | ft -AutoSize Server Name Label SizeInGB FreeInGB PercentFree BlockSize ------ ---- ----- -------- -------- ----------- --------- MYSQLHOST D:\ APP 500 160.79 32.16 4096 MYSQLHOST B:\ DATA 500 181.8 36.36 4096 MYSQLHOST E:\ DATA 50 49.89 99.79 4096 MYSQLHOST L:\ LOG 100 99.88 99.89 4096 MYSQLHOST T:\ TEMP 10 9.94 99.44 4096
3. Identify the files that can be moved using [YourDB].sys.database_files or Get-DbaDatabaseFile from dbatools
You could simply query YourDB.sys.database_files with TSQL to get the file list with the logical file name for each file and the physical location with size.
Alternatively, similar to the PowerShell call in the previous step, you could get the database files of a database and their size information using the dbatools function to understand which files can be moved around
PS C:\> Get-DbaDatabaseFile -SQLInstance MYSQLHOST -Databases YourDBToMaint | ft -AutoSize
4. Save the commands to OFFLINE/ONLINE/ROBOCOPY + other just-in-case items
When working with a large number of databases in an instance, it is easier to generate the commands to OFFLINE and ONLINE the databases than it is to do it by hand. The below SQL generates the ONLINE and OFFLINE commands in addition other “just-in-case” commands although those setting should not change when offlining and then onlining a database.
Important Note: Also, it generates the “soft file relocation” command “ALTER DATABASE MODIFY FILE” command in which you need to change what needs to be replaced in the existing path to the new path. In the example below, I am switching from folder E:\MSSQL13.MSSQLSERVER\MSSQL\DATA\ to D:\Data01 (a new volume mount-point). Edit the two variables a the top!
--The path from which you are moving files
DECLARE @FindString VARCHAR(100) = 'E:\MSSQL13.MSSQLSERVER\MSSQL\DATA\'
--The path to which you are moving files
DECLARE @ReplaceString VARCHAR(100) = 'D:\data01\'
SELECT DISTINCT
'ALTER DATABASE ' + QUOTENAME(DB_NAME(dbid)) +
' MODIFY FILE (name=''' + f.name + ''', filename='''
+ REPLACE(f.filename, @FindString, @ReplaceString)
+ ''');' AS SoftRelocateCommand,
'ALTER DATABASE ' + QUOTENAME(DB_NAME(dbid)) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;'+ CHAR(13) AS SetOfflineCommand,
'robocopy ' + @FindString + ' ' + @ReplaceString + ' ' + REPLACE(f.filename, @FindString, '') + ' /COPYALL /ZB /R:1 /W:1 /V /TEE /LOG:Robocopy.' + f.name + '.log' AS RoboCopyCommand,
'ALTER DATABASE ' + QUOTENAME(DB_NAME(dbid)) + ' SET ONLINE;'+ CHAR(13) AS SetOnlineCommand,
CASE WHEN d.is_read_only = 1
THEN 'ALTER DATABASE ' + QUOTENAME(DB_NAME(dbid)) + ' SET READ_ONLY WITH NO_WAIT;'+ CHAR(13)
ELSE ''
END AS JustInCase_SetReadOnlyCommand,
CASE WHEN d.is_broker_enabled = 1
THEN 'ALTER DATABASE ' + QUOTENAME(DB_NAME(dbid)) + ' ENABLE BROKER;'+ CHAR(13)
ELSE ''
END AS JustInCase_EnableServiceBroker,
CASE WHEN d.is_trustworthy_on = 1
THEN 'ALTER DATABASE ' + QUOTENAME(DB_NAME(dbid)) + ' SET TRUSTWORTHY ON;'+ CHAR(13)
ELSE ''
END AS JustInCase_EnableTrustWorthy,
'USE ' +QUOTENAME(d.name) + CHAR(13) + 'EXEC sp_changedbowner ''' + suser_sname(owner_sid) + ''' ;'+ CHAR(13) AS JustInCase_SetDBOwner
FROM master.dbo.sysaltfiles f
INNER JOIN sys.databases d
ON f.dbid = d.database_id
WHERE
DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
--We only look for files containing source path
AND filename LIKE @FindString + '%'
--Add additional conditions as necessary
Using PowerShell, save the information to a CSV file for record-keeping and for later use. You could use the Invoke-SQLCmd from the native PowerShell module or the Invoke-DBASQLCmd from the dbatools module. You must have one or the other module on your PC. If you have neither, run the above SQL by hand and export the results out to CSV.
#Runs the SQL above to generate ONLINE/OFFLINE commands
# and saves the results to a CSV file for future use!<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
[string] $sqlInstance = 'MyInstanceName'
[string] $sqlFile = 'c:\MyPathToSQLFromAbove\GenerateOfflineOnline.sql'
[string] $exportFile = 'c:\MyPathToSQLFromAbove\GenerateOfflineOnline.vsv'
[string] $sql = Get-Content $sqlFile |
Out-String
$rslt = Invoke-Sqlcmd `
-ServerInstance $sqlInstance `
-Database master `
-Query $sql `
-OutputAs DataRows
$rslt | Export-Csv $exportFile -Force -NoTypeInformation -Delimiter "|"
At this point, if you open the VSV file, it should have all the commands we would need later and should look like below. Rename the exported file extension and use the Excel wizard to import since we use | as the delimiter since the commands could themselves have embedded commas.
5. ALTER DATABASE MODIFY to relocate file pointers in the system database
The database can still be ONLINE and ATTACHED when you do this because only the internal system tables will be updated. All we are doing is making a change to point to the new location of the files we intend to move.
Here, I am only showing a sample to illustrate the syntax but you would use the command we generated and saved to a CSV file in the step above. Copy, Paste and Run.
ALTER DATABASE MY_DB
MODIFY FILE (name='MY_DB_FILE_03'
,filename='E:\DATA\MSSQL12.MYINST\MSSQL\DATA\MY_DB_FILE_03.ndf');
The location referenced in the above command is the new location of the file. Based on the logical name MY_DB_FILE_03, it knows what you are referencing. You could re-point as many files as you want at this point. You just have to remember to physically move them later.
5. ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
At this point, you need to OFFLINE your DB. Next-up, we physically move the files that you re-pointed in the previous step.
Here, I am only showing a sample to illustrate the syntax but you would use the command we generated and saved to a CSV file in the step above. Copy, Paste and Run.
ALTER DATABASE [YourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
6. Physically move the files to a new drive where there is adequate space using Robocopy
The re-pointed files should now be moved to their new location. To minimize risk, I recommend first copying the files, test everything after “onlining” (next step) and then remove the source files, instead of “moving” at the get-go.
The easiest way to copy files in a resumable fashion while still retaining all the attributes and permissions is to use RoboCopy that is bundled with Windows including Windows Servers. I would use the below option (borrowed from here)
robocopy source destination /E /ZB /DCOPY:T /COPYALL /R:1 /W:1 /V /TEE /LOG:Robocopy.log
The above command is to move all contents of a directory and sub-directories. Most likely, you will just move one or more files. To move a single file (say Test.mdf), you would use the command as in the example below:
robocopy E:\MSSQL13.MSSQLSERVER\MSSQL\DATA d:\data01 Test.mdf /COPYALL /ZB /R:1 /W:1 /V /TEE /LOG:Robocopy.log
Where COPYALL is to copy all file attributes, /ZB is for resumable copy, R for retrying (once), W for waiting (1 second) in between retries, V for verbose output and TEE to log to screen and log file and LOG to specify the log file in the current working directory.
Even if you don’t use Robocopy, just make sure that files in their new location have full control permission for the SQL Server service account.
You could use the below SQL to get the service account information
SELECT DSS.servicename,
DSS.startup_type_desc,
DSS.status_desc,
DSS.last_startup_time,
DSS.service_account,
DSS.is_clustered,
DSS.cluster_nodename,
DSS.filename,
DSS.startup_type,
DSS.status,
DSS.process_id
FROM sys.dm_server_services AS DSS;
If the service account does not have adequate permissions to the files, then you may get an error when you try to bring the database online in the next step.
Msg 5120, Level 16, State 101, Line 17
Unable to open the physical file “E:\DATA\MSSQL12.MYINST\MSSQL\DATA\MY_DB_FILE_03.ndf”. Operating system error 5: “5(Access is denied.)”.
7. ALTER DATABASE [YourDB] SET ONLINE;
Before you bring the databases online, check to see if all the files are where you expect them to be physically and logically. Run this SQL on the instance:
SELECT 'DIR ' + filename AS CheckFileExistence
FROM master.dbo.sysaltfiles f
WHERE DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
This generates the DIR command for all the files referenced in the instance. Copy the whole thing and run it on the server in the command prompt and make sure all of the files exist. If you find that some of the files do not exist, check to see why and make the corresponding update if necessary using “ALTER DATABASE MODIFY” with the right location.
Do not skip the above verification. It is easy and fast to check and fix paths now than later.
Once the verification is complete, all you have to do is bring your DB Online and everything should be fine. If you run into errors, please make sure that you set the permissions appropriately as noted in the previous step.
You would use the command we generated and saved to a CSV file to get all the databases ONLINE. Copy, Paste and Run.
That’s it! You have now moved files to free-up space on a certain drive that was full and you did it like a Pro!
SQL Jana 
Yes. The service account needs permissions on the file. Thank you for noting that.