Sql-server – Move database physical location using a loop for database names. MSSQL

sql servert-sql

I have a main database and several other databases that store documents in them.

I am trying to figure out a way to write a loop in my database move script that dynamically loops through the document databases and include them in my script however the amount of databases aren't fixed. For example I could have one documents database called AppDocuments1 or multiple document databases e.g. AppDocuments1, AppDocuments2, AppDocuments3 and so on.

Could someone help me with creating a loop that automatically loops the all databases so I can include them in my database script.

I have access to the number of databases there are in a field in the main product database. e.g. NoOfDocumentDatabases : value = 6 would indication 6 document databases all named as above.

Best Answer

This is a script I developed in the past for helping me migrate lots of databases from one drive to another. It only works on one drive at a time (figured it was safer and easier to manager that way) but you can easily change that.

SET NOCOUNT ON;

DECLARE @OldDriveLetter VARCHAR(2) = 'D:';
DECLARE @NewDriveLetter VARCHAR(2) = 'N:';

DECLARE @Script_SetDatabaseOffline AS NVARCHAR(MAX) = 'USE master;' + CHAR(13)
DECLARE @Script_ModifyMDFLocation AS NVARCHAR(MAX) = '' 
DECLARE @Script_ModifyLDFLocation AS NVARCHAR(MAX) = '' 
DECLARE @Script_SetDatabaseOnline AS NVARCHAR(MAX) = ''; 

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- SET DATABASES OFFLINE
SELECT @Script_SetDatabaseOffline = @Script_SetDatabaseOffline +
'
    ALTER DATABASE [' + D.[name] + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time          
    AND MF.[file_id] = 1; -- This ensures we de-dupe the result set so we don't have multiple dupe "ALTER SameDatabase SET OFFLINE" 

--PRINT @Script_SetDatabaseOffline

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- MODIFY MDF FILE LOCATION
SELECT @Script_ModifyMDFLocation = @Script_ModifyMDFLocation +
'
    ALTER DATABASE [' + D.[name] + '] 
    MODIFY FILE (name=''' + MF.[name] + ''', filename=''' + REPLACE(MF.physical_name, 'D:', @NewDriveLetter) + ''');
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time      
    AND MF.[type] = 0 -- MDF (ROWS)
    
--PRINT @Script_ModifyMDFLocation

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- MODIFY LDF LOCATION
SELECT @Script_ModifyLDFLocation = @Script_ModifyLDFLocation +
'
    ALTER DATABASE [' + D.[name] + '] 
    MODIFY FILE (name=''' + MF.[name] + ''', filename=''' + REPLACE(MF.physical_name, 'D:', @NewDriveLetter) + ''');
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time      
    AND MF.[type] = 1; -- LDF (LOGS)
    
--PRINT @Script_ModifyLDFLocation

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- SET DATABASES ONLINE
SELECT @Script_SetDatabaseOnline = @Script_SetDatabaseOnline +
'
    ALTER DATABASE [' + D.[name] + '] SET ONLINE;
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time      
    AND MF.[file_id] = 1; -- This ensures we de-dupe the result set so we don't have multiple dupe "ALTER SameDatabase SET OFFLINE"

--PRINT @Script_SetDatabaseOnline

/*
SELECT @Script_SetDatabaseOffline
SELECT @Script_ModifyMDFLocation 
SELECT @Script_ModifyLDFLocation 
SELECT @Script_SetDatabaseOnline;
*/

SELECT @Script_SetDatabaseOffline + @Script_ModifyMDFLocation + @Script_ModifyLDFLocation + @Script_SetDatabaseOnline;

The comments explain what's going on in details but in short it's basically using dynamic SQL and the sys schema objects to build out strings of the queries needed to reconfigure the locations of the MDFs and LDFs of all user databases. (You can add your own additional logic if you want to exclude certain user databases too.)

The steps it basically does are:

  1. Sets the databases offline
  2. Modifies the MDF locations
  3. Modifies the LDF locations

*You'll want to move the physical files between step 3 and 4 (because the databases have to be offline before you can move them).

  1. Sets the databases back online
Related Question