-
-
Save mrhaw/f4f3fcdb39a6ed2e2bbba32f452cd11f to your computer and use it in GitHub Desktop.
For moving Database Files off of C: drive. Accepts a single Database, or runs for *all* databases. In testing, it took about one minute for each GB of DB size, so plan accordingly accordingly. There is a list of excluded databases, allowing you to precisely target databases.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @WorkingSQL VARCHAR(8000) | |
,@NewPath VARCHAR(8000) = 'G:\SQL Data\' /* Root location to move files */ | |
,@TargetDatabase sysname = '%'; /* Specify a singular database, or % for All Databases */ | |
SET NOCOUNT ON; | |
/* Sanitize path */ | |
IF RIGHT(@NewPath, 1) <> '\' | |
BEGIN | |
SET @NewPath = @NewPath + '\'; | |
END; | |
/* Check to ensure directory is valid and accessible by SQL Service */ | |
CREATE TABLE #Results | |
( | |
FileExists INT | |
,IsDirectory INT | |
,ParentDirExists INT | |
); | |
INSERT INTO #Results | |
EXEC master..xp_fileexist @NewPath; | |
IF NOT EXISTS ( | |
SELECT 1 | |
FROM #Results AS r | |
WHERE r.IsDirectory = 1 | |
) | |
BEGIN | |
SELECT 'Invalid Location Specified'; | |
END; | |
ELSE | |
BEGIN | |
SET NOCOUNT OFF; | |
DECLARE @DatabaseFiles TABLE | |
( | |
DatabaseName sysname | |
,DatabaseID SMALLINT | |
,FileSize INT | |
,OriginalPath VARCHAR(MAX) | |
,NewPath VARCHAR(MAX) | |
,MoveCommand VARCHAR(MAX) | |
,Processed BIT DEFAULT (0) | |
,FileMoved BIT DEFAULT (0) | |
); | |
INSERT INTO @DatabaseFiles | |
( | |
DatabaseName | |
,DatabaseID | |
,FileSize | |
,MoveCommand | |
,OriginalPath | |
,NewPath | |
) | |
SELECT t.DatabaseName | |
,t.DatabaseID | |
,t.FileSize | |
,t.MoveCommand | |
,t.OriginalPath | |
,t.NewPath | |
FROM ( | |
SELECT s.name AS DatabaseName | |
,s.dbid AS DatabaseID | |
,mf.size AS FileSize | |
,'ALTER DATABASE ' + CASE | |
WHEN s.name LIKE '% %' THEN '[' | |
ELSE '' | |
END + s.name + CASE | |
WHEN s.name LIKE '% %' THEN ']' | |
ELSE '' | |
END + ' MODIFY FILE ( NAME = ''' + mf.name | |
+ ''', FILENAME = ''' + @NewPath | |
+ REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name)) - 1)) + ''');' AS MoveCommand | |
,mf.physical_name AS OriginalPath | |
,@NewPath + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name)) - 1)) AS NewPath | |
FROM sys.sysdatabases AS s | |
INNER JOIN sys.master_files AS mf ON s.dbid = mf.database_id | |
WHERE 1 = 1 | |
/* Exclude system databases. Comment out if you wish to move system databases as well */ | |
AND s.name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerTempDB') | |
) AS t | |
/* Exclude DB files that are not already in new location */ | |
WHERE 1 = 1 | |
AND t.OriginalPath <> t.NewPath | |
AND t.DatabaseName LIKE @TargetDatabase; | |
BEGIN | |
/* While unprocessed Databases remain */ | |
WHILE ( | |
SELECT COUNT(DISTINCT cl.DatabaseID) | |
FROM @DatabaseFiles AS cl | |
WHERE cl.Processed = 0 | |
) > 0 | |
BEGIN | |
DECLARE @WorkingDatabaseID SMALLINT | |
,@WorkingDatabaseName sysname; | |
/* Set Working Database, largest first */ | |
SELECT TOP 1 | |
@WorkingDatabaseID = cl.DatabaseID | |
,@WorkingDatabaseName = cl.DatabaseName | |
FROM @DatabaseFiles AS cl | |
WHERE cl.Processed = 0 | |
ORDER BY | |
cl.FileSize DESC; | |
PRINT 'Begin Processing: ' + @WorkingDatabaseName; | |
/* Kill any existing connections, then take Working Database offline */ | |
PRINT 'ALTER DATABASE [' + @WorkingDatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'; | |
PRINT 'ALTER DATABASE [' + @WorkingDatabaseName + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE'; | |
PRINT 'ALTER DATABASE [' + @WorkingDatabaseName + '] SET OFFLINE;'; | |
EXEC ('ALTER DATABASE [' + @WorkingDatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'); | |
EXEC ('ALTER DATABASE [' + @WorkingDatabaseName + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE'); | |
EXEC ('ALTER DATABASE [' + @WorkingDatabaseName + '] SET OFFLINE;'); | |
/* Update Database Path and Move Files */ | |
WHILE ( | |
SELECT COUNT(1) | |
FROM @DatabaseFiles AS cl | |
WHERE cl.DatabaseID = @WorkingDatabaseID | |
AND cl.FileMoved = 0 | |
) > 0 | |
BEGIN | |
SET NOCOUNT ON; | |
DECLARE @WorkingOriginalPath VARCHAR(MAX) | |
,@WorkingNewPath VARCHAR(MAX); | |
/* Get top 1 unmoved database */ | |
SELECT TOP 1 | |
@WorkingOriginalPath = cl.OriginalPath | |
,@WorkingNewPath = cl.NewPath | |
,@WorkingSQL = cl.MoveCommand | |
FROM @DatabaseFiles AS cl | |
WHERE cl.DatabaseID = @WorkingDatabaseID | |
AND cl.FileMoved = 0 | |
ORDER BY | |
cl.FileSize DESC; | |
/* Update catalog location (ALTER DATABASE...MODIFY FILE...) */ | |
PRINT @WorkingSQL; | |
EXEC (@WorkingSQL); | |
/* Set xp_cmdshell command to physically move the files from OriginalPath to NewPath */ | |
SELECT @WorkingSQL = 'MOVE "' + @WorkingOriginalPath + '" "' + @WorkingNewPath + '"'; | |
PRINT @WorkingSQL; | |
EXEC sys.xp_cmdshell @WorkingSQL, no_output; | |
/* Update file has been moved */ | |
UPDATE cl | |
SET cl.FileMoved = 1 | |
FROM @DatabaseFiles AS cl | |
WHERE cl.OriginalPath = @WorkingOriginalPath | |
AND cl.NewPath = @WorkingNewPath | |
AND cl.DatabaseID = @WorkingDatabaseID; | |
SET NOCOUNT OFF; | |
END; | |
/* Once DB Files have been moved, set Database Online */ | |
PRINT 'ALTER DATABASE [' + @WorkingDatabaseName + '] SET ONLINE;'; | |
EXEC ('ALTER DATABASE [' + @WorkingDatabaseName + '] SET ONLINE;'); | |
PRINT 'End Processing: ' + @WorkingDatabaseName; | |
/* Set Database as Processed */ | |
UPDATE c | |
SET c.Processed = 1 | |
FROM @DatabaseFiles AS c | |
WHERE c.DatabaseID = @WorkingDatabaseID; | |
END; | |
END; | |
END; | |
DROP TABLE #Results; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment