Relocate User DBs in an Instance

Have you ever inherited an instance and found that the user databases all live on the C: or some other location you despise?  If you’re OCD like me you want all your mdf’s, ndf’s and ldf’s right where you want them in a standard place that you’ve configured for optimal IO to ensure your instance hums, not where some n00b let those files live after clicking Next, Next, Finish on the instance installation GUI, right?  Well here’s a little script that will help you move all user DBs from where ever they are to where they ought to be … hope it helps!

USE master;
GO
 
 
 
DECLARE @DatabaseName sysname;
DECLARE @FileName nvarchar(260);
DECLARE @FileType nvarchar(60);
DECLARE @LogicalName sysname;
--	Initialize these variables to your new path's
DECLARE @NewDataFileLocation nvarchar(260) = N'E:\mssql\instance_name\data\';
DECLARE @NewLogFileLocation nvarchar(260) = N'F:\mssql\instance_name\data\';
 
DECLARE @ScriptPart1 nvarchar(MAX)= CHAR(10) + CHAR(13) + N'--	Run this part 1st to set user DBs in OFFLINE mode	-----------------------------------------';
DECLARE @ScriptPart2 nvarchar(MAX)= CHAR(10) + CHAR(13) + N'--	Run this part 2nd to confgure DB file paths	-------------------------------------------------';
DECLARE @scriptPart3 nvarchar(MAX)= CHAR(10) + CHAR(13) + N'--	Run this part 3rd to set user DBs back ONLINE	---------------------------------------------';
 
 
--	Build script to set DB's offline	--------------------------------------------------------------------------------------------------------------------------------------------
DECLARE DBInfo CURSOR FAST_FORWARD
FOR
    SELECT  DatabaseName = d.name
    FROM    sys.databases AS d
    WHERE   d.database_id > 4
    ORDER BY 1;
 
OPEN DBInfo;
FETCH NEXT FROM DBInfo INTO @DatabaseName;
 
PRINT @ScriptPart1;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ScriptPart1 = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET OFFLINE WITH ROLLBACK IMMEDIATE;
';
 
        PRINT @ScriptPart1;
        FETCH NEXT FROM DBInfo INTO @DatabaseName;
 
    END;
CLOSE DBInfo;
 
 
 
--	Build script to move DB files	--------------------------------------------------------------------------------------------------------------------------------------------
DECLARE DBFiles CURSOR FAST_FORWARD
FOR
    SELECT  DatabaseName = d.name
          , LogicalName = f.name
          , FileName = f.physical_name
          , FileType = f.type_desc
    FROM    sys.databases AS d
            LEFT JOIN sys.master_files AS f ON d.database_id = f.database_id
    WHERE   d.database_id > 4
    ORDER BY 1;
 
OPEN DBFiles;
FETCH NEXT FROM DBFiles INTO @DatabaseName, @LogicalName, @FileName, @FileType;
 
PRINT @ScriptPart2;
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @FileType = 'LOG'
            SET @ScriptPart2 = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' MODIFY FILE (NAME = ' + QUOTENAME(@LogicalName) + N', FILENAME = '
                + QUOTENAME(REPLACE(@FileName, LEFT(@FileName, LEN(@FileName) - CHARINDEX('\', REVERSE(@FileName)) + 1), @NewLogFileLocation), '''') + N');
';
        ELSE
            SET @ScriptPart2 = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' MODIFY FILE (NAME = ' + QUOTENAME(@LogicalName) + N', FILENAME = '
                + QUOTENAME(REPLACE(@FileName, LEFT(@FileName, LEN(@FileName) - CHARINDEX('\', REVERSE(@FileName)) + 1), @NewDataFileLocation), '''') + N');
';
        PRINT @ScriptPart2;
        FETCH NEXT FROM DBFiles INTO @DatabaseName, @LogicalName, @FileName, @FileType;
    END;
DEALLOCATE DBFiles;
 
 
PRINT CHAR(10) + CHAR(13) + N'--	Now perform the copy/paste of the actual DB files in the OS	------------------------------------------------------------------------------------------------------
 
'
 
--	Build script to set DB's online	--------------------------------------------------------------------------------------------------------------------------------------------
OPEN DBInfo;
FETCH NEXT FROM DBInfo INTO @DatabaseName;
 
 
PRINT @scriptPart3;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @scriptPart3 = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET ONLINE;
';
 
        PRINT @scriptPart3;
        FETCH NEXT FROM DBInfo INTO @DatabaseName;
    END;
 
 
DEALLOCATE DBInfo;

Leave a Reply

Your email address will not be published. Required fields are marked *