Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
For moving Database Files to new location. Does a copy, so some cleanup is necessary. Accepts a single Database, or runs for *all* databases. In testing, it took about one minute for each 10GB of DB size, so plan accordingly accordingly. There is a list of excluded databases, allowing you to precisely target databases.
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;
/* Enable xp_cmdshell */
EXEC sys.sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
/* 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 = 'COPY "' + @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