So you inherit a server and find the previous owners had left the default data and log folders on the C: drive and at the same time they managed to put half your databases are on the correct drives and the others were left getting cosy with your operating system.
Thankfully remediation isn’t too painful but we’ll need a brief outage for it. We’ll start by taking the database offline followed by moving the files to the correct directories. Whilst that’s moving we can tell SQL where the new location will be, and once the relocation is complete we can bring the database online. Your script would look something like this:
/* work needs to be done from the master database */
USE [master];
/* Take the database offline*/
ALTER DATABASE MyAppDb SET OFFLINE;
/* Move the actual data files to the new disk/s */
/* Update the file paths for new locations */
ALTER DATABASE MyAppDb MODIFY FILE (NAME = MyAppDb, FILENAME = 'E:\Data\MyAppDb.mdf');
ALTER DATABASE MyAppDb MODIFY FILE (NAME = MyAppDb_Log, FILENAME = 'F:\Log\MyAppDb_log.ldf');
/* Now bring the database online */
ALTER DATABASE MyAppDb SET ONLINE;
If you see an error along the lines of Could not restart database "MyAppDb". Reverting to the previous status. then you may have made a mistake with the pathing/naming of the files or may need to make changes to the security if its a new drive/path you’re setting up so double check those. The database may show in recovery following the error but just correct the issue and you should be able to bring it online without issue.
Changing the Default Location
Once you’ve got your files moved you’ll want to correct the default location so no new databases start popping up on the old drive. Fortunately this is really straight forward via the Server Properties dialog however if you’d like to script this as part of a deployment for example it gets a little messier. These aren’t options we can set directly in SQL as they’re stored within the registry. To update these paths via a script we use the below (paths in the registry are valid for SQL Server version 2012 and newer):
/* Again we want to be in the master db */
USE [master];
/* Note that you may need to change 'MSSQL15.MSSQLSERVER' in the registry paths below based
on your version and configuration, in this case we're using SQL 2019 with the default instance */
/* Update the default data directory */
EXEC xp_instance_regwrite
'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer',
'DefaultData', REG_SZ, 'E:\Data';
/* Update the default log directory */
EXEC xp_instance_regwrite
'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer',
'DefaultLog', REG_SZ, 'F:\Logs';
Its worth noting that as these are stored in the registry they’re only reloaded at start up so once the changes have been made you’ll want to restart the SQL service to make sure the new values take effect.
Andy Brownsword 