Moving SQL Server databases

It's not uncommon to find SQL Server installations where the physical file layout is less than optimal. We have a client who is having some performance issues and in initial discovery, we observed that they had the system databases on the C drive and had put their user databases and log on the D. The challenge you can run into, and they are experiencing it, is that heavy workloads can saturate your I/O subsystem and that brings everything to a crawl. There's also a risk of running your C drive out of disk space that way which can cause the OS itself to stop responding. The client has already purchased a SAN and as part of our effort, we're going to relocate user and system databases onto the SAN.

There are lots of permutations on how to lay that out but the general rule is to isolate logs, data files, tempdb and if you can spare it, the backups from each other. Knowing that's the right design is one thing, but how do I go about fixing a broken one? MSDN to the rescue, move system databases.

I started by moving tempdb. Since tempdb doesn't require moving the data or log files, it was the easiest to deal with. The reason you don't need to move them, is that they are recreated whenever the system is restarted. I went through the example and was able to change the location of my tempdb as easy as can be.

The "other" system databases, those seemed a little more daunting because now I was going to be dealing with TSQL and OS commands. Yes, it's just "move" but still, I'm now going to be in two different environments and I'll need to repeat the process for all the databases. At this point, I start to get nervous about the amount of manual work to be done and more importantly the opportunity for errors to creep in. I don't know if I'm going to be pushing buttons at the client site or if they'll be at the keyboard. An automated solution sounds like it might be called for.

I wanted to go PowerShell but I held off on that approach as I wanted to deal with this situation a few times before I felt comfortable that I'd stepped on all the landmines. Instead, I wrote a TSQL script to generate my changes. The script generates 4 sets of commands for each row in sys.master_files. The first 2 columns change the file locations in the database and on disk. The last 2 columns undo those changes. Pro-tip: always have a backout strategy. For those looking at my SSMS Templates, in my Utility folder, I called this one DatabaseFileMover.sql As of right now, that code looks like the following

   1:  DECLARE

   2:      @NewLogLocation varchar(256) = 'L:\SQLLog'

   3:  ,   @NewBackupLocation varchar(256) = 'M:\SQLBackups'

   4:  ,   @NewDataLocation varchar(256) = 'S:\SQLData'

   5:  ,   @NewTempDBLocation varchar(256) = 'T:\TempDB'

   6:  ,   @AlterCommand varchar(512) = ' 

   7:  ALTER DATABASE <dbname/>  

   8:  MODIFY FILE (NAME = <name/>, FILENAME = ''<new_location/>'');'

   9:  ,   @MoveCommand varchar(512) = 'MOVE "<old_location/>" "<new_location/>"'

  10:  ,   @DeleteCommand varchar(512) = 'DEL "<old_location/>"'; 

  11:    

  12:  SELECT

  13:      CASE

  14:          WHEN D.type_desc = 'ROWS' AND D.DatabaseName <> 'tempdb' 

  15:              -- Undo version 

  16:              -- THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', D.databaseName), '<name/>', D.LogicalName), '<new_location/>', D.PhysicalName) 

  17:              THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', QUOTENAME(D.databaseName)), '<name/>', D.LogicalName), '<new_location/>', @NewDataLocation + '\' + D.BaseFileName) 

  18:          WHEN D.type_desc = 'LOG' AND D.DatabaseName <> 'tempdb'  

  19:              -- Undo version 

  20:              -- THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', D.databaseName), '<name/>', D.LogicalName), '<new_location/>', D.PhysicalName) 

  21:              THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', QUOTENAME(D.databaseName)), '<name/>', D.LogicalName), '<new_location/>', @NewLogLocation + '\' + D.BaseFileName) 

  22:          WHEN D.DatabaseName = 'tempdb'  

  23:              THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', QUOTENAME(D.databaseName)), '<name/>', D.LogicalName), '<new_location/>',  @NewTempDBLocation + '\' + D.BaseFileName) 

  24:          ELSE NULL 

  25:      END AS AlterCommand 

  26:  ,   CASE  

  27:          WHEN D.type_desc = 'ROWS' AND D.DatabaseName <> 'tempdb'  

  28:              THEN REPLACE(REPLACE(REPLACE(@MoveCommand, '<old_location/>', D.PhysicalName), '<name/>', D.LogicalName), '<new_location/>', @NewDataLocation + '\' + D.BaseFileName) 

  29:          WHEN D.type_desc = 'LOG' AND D.DatabaseName <> 'tempdb'  

  30:              THEN REPLACE(REPLACE(REPLACE(@MoveCommand, '<old_location/>', D.PhysicalName), '<name/>', D.LogicalName), '<new_location/>', @NewLogLocation + '\' + D.BaseFileName) 

  31:          WHEN D.DatabaseName = 'tempdb'  

  32:              THEN REPLACE(REPLACE(REPLACE(@DeleteCommand, '<old_location/>', D.PhysicalName), '<name/>', D.LogicalName), '<new_location/>', @NewLogLocation + '\' + D.BaseFileName) 

  33:      END AS MoveCommand 

  34:  ,   CASE 

  35:          WHEN D.type_desc = 'ROWS' AND D.DatabaseName <> 'tempdb'  

  36:              THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', QUOTENAME(D.databaseName)), '<name/>', D.LogicalName), '<new_location/>', D.PhysicalName) 

  37:          WHEN D.type_desc = 'LOG' AND D.DatabaseName <> 'tempdb'  

  38:              THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', QUOTENAME(D.databaseName)), '<name/>', D.LogicalName), '<new_location/>', D.PhysicalName) 

  39:          WHEN D.DatabaseName = 'tempdb'  

  40:              THEN REPLACE(REPLACE(REPLACE(@AlterCommand, '<dbname/>', QUOTENAME(D.databaseName)), '<name/>', D.LogicalName), '<new_location/>',  D.PhysicalName) 

  41:          ELSE NULL 

  42:      END AS UndoAlterCommand 

  43:  ,   CASE  

  44:          WHEN D.type_desc = 'ROWS' AND D.DatabaseName <> 'tempdb'  

  45:              THEN REPLACE(REPLACE(REPLACE(@MoveCommand, '<new_location/>', D.PhysicalName), '<name/>', D.LogicalName), '<old_location/>', @NewDataLocation + '\' + D.BaseFileName) 

  46:          WHEN D.type_desc = 'LOG' AND D.DatabaseName <> 'tempdb'  

  47:              THEN REPLACE(REPLACE(REPLACE(@MoveCommand, '<new_location/>', D.PhysicalName), '<name/>', D.LogicalName), '<old_location/>', @NewLogLocation + '\' + D.BaseFileName) 

  48:          WHEN D.DatabaseName = 'tempdb'  

  49:              THEN REPLACE(REPLACE(REPLACE(@DeleteCommand, '<new_location/>', D.PhysicalName), '<name/>', D.LogicalName), '<old_location/>', @NewLogLocation + '\' + D.BaseFileName) 

  50:      END AS UndoMoveCommand 

  51:  FROM 

  52:  ( 

  53:      SELECT D.* 

  54:      ,   LEFT(D.PhysicalName, D.LastSlash) AS CurrentFolder 

  55:      ,   RIGHT(D.PhysicalName, LEN(D.PhysicalName) - D.LastSlash - 1) AS BaseFileName 

  56:      FROM 

  57:      ( 

  58:          SELECT  

  59:              name AS LogicalName 

  60:          ,   physical_name AS PhysicalName 

  61:          ,   LEN(MF.physical_name) - CHARINDEX('\', REVERSE(MF.physical_name)) AS LastSlash 

  62:          ,   MF.type_desc 

  63:          ,   DB_NAME(MF.database_id) AS DatabaseName 

  64:          ,   MF.database_id 

  65:          FROM 

  66:              sys.master_files AS MF 

  67:          --WHERE 

  68:          --    MF.database_id < 5 

  69:      ) D 

  70:  ) D 

It generated output like

AlterCommand	MoveCommand	UndoAlterCommand	UndoMoveCommand
ALTER DATABASE [master] MODIFY FILE (NAME = master, FILENAME = 'S:\SQLData\master.mdf');	MOVE "C:\Program Files\Microsoft SQL Server\MSSQL11.DEV2012\MSSQL\DATA\master.mdf" "S:\SQLData\master.mdf"	ALTER DATABASE [master] MODIFY FILE (NAME = master, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEV2012\MSSQL\DATA\master.mdf');	MOVE "S:\SQLData\master.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL11.DEV2012\MSSQL\DATA\master.mdf"
ALTER DATABASE [master] MODIFY FILE (NAME = mastlog, FILENAME = 'L:\SQLLog\mastlog.ldf');	MOVE "C:\Program Files\Microsoft SQL Server\MSSQL11.DEV2012\MSSQL\DATA\mastlog.ldf" "L:\SQLLog\mastlog.ldf"	ALTER DATABASE [master] MODIFY FILE (NAME = mastlog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEV2012\MSSQL\DATA\mastlog.ldf');	MOVE "L:\SQLLog\mastlog.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL11.DEV2012\MSSQL\DATA\mastlog.ldf"

That looks about right for what I was intending. Column 0 was saved out to AlterCommand.sql. Column 1 to MoveCommand.bat. Column 2 became UndoAlterCommand.sql and Column 3 became UndoMoveCommand.bat.

I stared at the pending alter statements long and hard. Had a cup of tea and finally hit F5 but it failed. See, I didn't have any drive on my machine but C. Instead, I thought I could fake it out by using my friend subst. It works great for the SSIS scenarios I have where I need to pretend I have a particular path available for a hardcoded thing. But, SQL Server is smarter than that. It could see through my shenanigans and determine that my S, L and T drives were fakes and would not allow the ALTER DATABASE statements to pass.

After replace all those fake drive letters with actual paths on C:, I was ready. I fired off the alter commands and they all worked. SQL Server reported that those changes would take effect next time I restarted the service. Goodie, I double click my MoveFile.bat and it promptly throws up because those files are all in an administrator location (thanks windows 8). I couldn't see it failing though as the batch script ended too quickly. If you add the command PAUSE at the end of a .bat file, it will keep the window open until someone hits a key. I fix the file, right click and run as admin but those files are still in use. Oh yeah, *stop* the service, then move the files. I stop SQL Server and re-run the batch script and see my mdfs and ldfs move into the right folders. Great success.

I started the service and what was that flash? Oh crap, why is it still red? Eventviewer, eventvwr, got me started by reminding me that I have an error log available that I can read without SSMS. It kindly informed me that on start up it couldn't find my master database. The funny thing was, it was still looking in C:\Program Files... Sonofagun but fortunately I had my UndoMoveCommand.bat script there and after running as admin, it put back the files in their correct spots. Restart SQL Server and it's still broken. This time it found master data and log files but it was looking for model and msdb in C:\FakeDriveLocations. What the hell? The ALTER statement worked for everything *but* master. That seems unlikely. I looked back at the alter scripts and there didn't seem to be any diference between master and model commands. Inspiration finally struck after reading and rereading the errorlog file and I looked at the service in SQL Server Configuration Manager (SQLServerManager11.msc). There on the Advanced tab was startup parameters of "-dC:\Program Files...\master.mdf;-e...ErrorLog;-lC:\Program Files...\mastlog.ldf"

Database engine startup options specify that -d and -l specify where the master database data and log file should live. To heck with start up parameters, I already specified where the files should live before I broke the server, use that! So I copied out the existing parameters and cut out the -d and -l options. I figured the error log was fine where it was and restarted the service. Again SQL Server failed to start but this time the error message simply specified that it couldn't find master.mdf and mastlog.ldf-no path specified.

Intriguing, but at this point, I just want to get my database back up. Once again, I modify the start up parameters and provide explicit paths for the master database using the new paths. This time when I start the service, it starts and all my databases are available. Nothing outrageous in the error log either. I start SQL Agent and it too seems fine. Huzzah!

I undo the whole process by running UndoAlterStatement.sql, stopping the SQL Service, running UndoMoveCommand.bat as an admin, reverting the startup parameters for the service and finally restarting the service. Once again, green lights and my database seems operational. I think I have a winner.

How I plan to migrate database files

This, as with all information on this blog, is merely what I'm doing. Doing so in your environment may cause loss of data, irreparable harm and loss of life. Procede at your own risk.

1. Take a full backup up of everything and ensure it was good
2. Edit the above template script to ensure the files are going to valid locations
3. Run the script and save out each column as described. Be sure to add the PAUSE command at the end of the .bat files
4. Run the AlterCommand script against the server "sqlcmd -S yourserver -i AlterCommand.sql"
5. Use SQL Server Configuration Manager to stop the SQL Server service
6. Change the Startup Parameters to use the new locations for the master mdf and ldf
7. Run the MoveCommand.bat as an admin
8. Restart the SQL Service and check the ErrorLog
9. Connect to the instance via SSMS and ensure all databases are online and accessible
10. Check for database mail being configured and send an email