Introduction
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.
The Script
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | DECLARE @file_path nvarchar(520), @log_path nvarchar(520) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @file_path OUTPUT SELECT 'EXEC sp_configure ''show advanced options'', ''1'' GO RECONFIGURE GO EXEC sp_configure ''xp_cmdshell'', ''1'' GO RECONFIGURE GO' SELECT 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] SET OFFLINE WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILENAME = ''' + @file_path + '\' + mf.name + '.mdf'') GO GO GO GO GO ' 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'' GO GO GO GO' |
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
Conclusion
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.
