Today’s script is one that I had not planned on blogging so soon but since Paul Randal just talked about moving SQL Server files around for TechNet Magazine, it seemed like a good time to break this one out.
This script is a little different in that it is a script that creates a script, a “turducken” script if you will. The idea here is to run this script and let it output to text then take those results, paste them into a new window, review the resulting script and maybe even run it.
The script starts out by getting the default data and log file locations from SQL Server by checking the registry, using a method learned by watching Profiler while checking the location with Management Studio. (I often comment out these lines to change to specific locations.) The script then begins building the string to output by creating the command to turn xp_cmdshell on. A lot of people, including me, have a policy against xp_cmdshell being turned on on their servers but in cases like this where it is turned on to be used and turned right back off I feel I can get away with it. The next step is to create alter database scripts to take the databases offline. Next, the alter database statements and DOS file move commands are created. The command to set the database online is then added and finally, xp_cmdshell is turned back off.
Updated 07/14/2010 to replace move commands with copy to make sure the files are still good before they are deleted. This does add manual cleanup but the trade off is not having to find out how good your last backup is. Thanks to Paul Randal (Blog|Twitter) and Buck Woody (Blog|Twitter) for pointing this out.
DECLARE @file_path nvarchar(520),
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @file_path OUTPUT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @log_path OUTPUT
SELECT 'EXEC sp_configure ''show advanced options'', ''1''
EXEC sp_configure ''xp_cmdshell'', ''1''
SELECT 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILENAME = ''' + @file_path + '' + mf.name + '.mdf'')
ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf2.name + '], FILENAME = ''' + @log_path + '' + mf2.name + '.ldf'')
EXEC xp_cmdshell ''copy /Y "' + mf.physical_name + '" "' + @file_path + '' + mf.name + '.mdf"''
EXEC xp_cmdshell ''copy /Y "' + mf2.physical_name + '" "' + @log_path + '' + mf2.name + '.ldf"''
ALTER DATABASE [' + DB_NAME(mf.database_id) + '] SET ONLINE
FROM sys.master_files mf
INNER JOIN sys.master_files mf2
ON mf.database_id = mf2.database_id
WHERE DB_NAME(mf.database_id) NOT IN ('master', 'model', 'msdb', 'tempdb') and mf.type_desc = 'ROWS' and mf.file_id = 1 and mf2.type_desc = 'LOG'
AND (mf.physical_name != @file_path + '' + mf.name + '.mdf' OR mf2.physical_name != @log_path + '' + mf2.name + '.ldf')
order by mf.name
SELECT 'EXEC sp_configure ''xp_cmdshell'', ''0''
EXEC sp_configure ''show advanced options'', ''0''
Would this Work for TempDB?
Yes and no. The resulting script can be used to alter the location of TempDB but SQL Server must be stopped and started to move the files. There would also have to be manual cleanup in that the old TempDB files would have to be deleted.
Would this Work for System Databases other than TempDB?
No. There is a lot involved in moving a system database. Detailed instructions can be found here: http://msdn.microsoft.com/en-us/library/ms345408.aspx
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.