Here are several trivial scripts for updating the system catalog, moving files via robocopy, and adjusting filegrowth values. I hope someone finds them useful.
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 | -- generate system catalog changes -- *** note: need to manually edit destination path volume letter in output *** SELECT 'ALTER DATABASE ' + DB_NAME() + ' SET OFFLINE' SELECT 'ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE ( NAME = ' + NAME + ', FILENAME = N'+ filename + ' );' FROM dbo.sysfiles -- where filename like 'i:\%' -- order by size desc SELECT 'ALTER DATABASE ' + DB_NAME() + ' SET ONLINE' -- generate robocopy statements to be executed under command prompt -- *** note: need to manually edit destination path volume letter in output *** SELECT 'RoboCopy ' + LEFT(filename,(LEN(filename) - CHARINDEX('\', REVERSE(filename)))+1) + ' ' + LEFT(filename,(LEN(filename) - CHARINDEX('\', REVERSE(filename)))+1) + ' ' + RIGHT(filename,(CHARINDEX('\', REVERSE(filename)))-1) from sysfiles -- modify filegrowth SELECT 'ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE ( NAME = ' + NAME + ', Filegrowth = 128MB );' FROM dbo.sysfiles |
