SQL Move multiple databases from C: to D: at once
I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD.
Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive.
I have found this MSDN article, but that seems to be the procedure to move only one database.
Is there an automatic way or script to move all my databases at once?
|
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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
use master; go SET NOCOUNT ON print '----------------------------------------------------------------------------------' print '--Script for Moving Multiple database files to a new drive / ' + CONVERT(varchar(256),getdate() ) print '----------------------------------------------------------------------------------' print '' DECLARE @dbname nvarchar(128) DECLARE @DestPath nvarchar(256) --Set here the new destination path of the file set @DestPath = 'T:\Data\' ------------------------------------------------ --Filter: HD Databases ------------------------------------------------ DECLARE DBList_cursor CURSOR FOR Select name from sys.databases --where name like '<FIlter Something>' ---------------------------------------------- OPEN DBList_cursor FETCH NEXT FROM DBList_cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN declare @output_script varchar(max) --Output of the generated script declare @mdf_orig_path nvarchar(256) --Original datbase file path declare @cmdstring nvarchar(256) --Command String declare @CursorDeclare varchar(max) --Cursor declaration command declare @Originalfilename varchar(max) -- local @CursorDeclare command declare @filename varchar(max) -- local @CursorDeclare command declare @LogicalFileaame varchar(max) -- Logical FileName --Set null into @output script set @output_script='' --Generate Databse Cursor declaration command set @CursorDeclare='DECLARE DBFiles_cursor CURSOR FOR select [filename], [name] from '+ @dbname + '.sys.sysfiles' --Cursor Declaration execute (@CursorDeclare) OPEN DBFiles_cursor FETCH NEXT FROM DBFiles_cursor INTO @filename, @LogicalFileaame --For RollBack Option select @Originalfilename = @filename --Modify Physical FileName if (@filename like '%.mdf') begin select @mdf_orig_path = @filename IF(CHARINDEX('\', @filename) > 0) select @filename = RIGHT(@filename, CHARINDEX('\', REVERSE(@filename)) -1) select @filename = @DestPath + @filename select @cmdstring = ' ''copy' + ' ' + '"'+ @mdf_orig_path + '"' + ' ' + '"' + @filename +'"' + '''' --Get Logical FileNAme end print CHAR(10) print '-----------------------------------------' print @dbname print '-----------------------------------------' print CHAR(10) print 'print ''Start'' + CONVERT(varchar(256), getdate() ) ' print '---Offline Database' + @dbname print 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE' + CHAR(10) + 'GO' print 'exec master..xp_cmdshell' + ' ' + @cmdstring + CHAR(10) print '--For RollBack Use this:ALTER DATABASE ' + @dbname +' MODIFY FILE ( NAME =' + @LogicalFileaame +', FILENAME =' + @Originalfilename + ')' + CHAR(10) print 'ALTER DATABASE ' + @dbname +' MODIFY FILE ( NAME =' + @LogicalFileaame +', FILENAME =' + '''' + @DestPath + @dbname + '.mdf'' )' +CHAR(10) print '---ONline Database' + @dbname print 'ALTER DATABASE ' + @dbname + ' SET ONLINE WITH ROLLBACK IMMEDIATE GO' WHILE @@FETCH_STATUS = 0 BEGIN set @output_script=@output_script+' (FILENAME = '''+ @filename +'''),' FETCH NEXT FROM DBFiles_cursor INTO @filename, @LogicalFileaame END set @output_script=SUBSTRING(@output_script,0,len(@output_script)) CLOSE DBFiles_cursor DEALLOCATE DBFiles_cursor FETCH NEXT FROM DBList_cursor INTO @dbname END CLOSE DBList_cursor DEALLOCATE DBList_cursor |
This script will return a batch of statements that you can run:
|
1 2 3 4 5 |
SELECT d.name as db, f.name, physical_name, f.state_desc, 'ALTER DATABASE ['+d.name+'] MODIFY FILE (name='''+f.name+''' ,filename='''+replace(physical_name,'C:\database','D:\whatever')+'''); ' as DetachCommand, 'ALTER DATABASE ['+d.name+'] SET ONLINE' as ReattachCommand from sys.master_files f inner join sys.databases d on d.database_id=f.database_id |