MSSQL - Simplify and Automate Refresh Operations

One of the most recurring tasks of an MSSQL DBA is refreshing Databases. This task can quickly get boring as you connect to one instance, dump the database, copy it accross the network and then restore it on the other side, remapping manually Data and Log file logical names to the new physical file structure on the target Database.

The script below aims at making your life easier by scripting both steps of a refresh process, leveraging a network file share to host the dump file :

• Section 1 will export the Database to a defined network share
• Section 2 will load the Database dump file, analyze the file structure, and remap logical data and log file paths to restore the database in the right place

0. Pre-requisites

The only pre-requisite of this script is to have a shared folder on the network on which the dump will be performed. For security reasons, it might be good to restrict access to that folder and only grant access to service accounts running your MSSQL instances in addition to your DBA accounts.

1. Dumping the Source Database

• Connect to the source instance with a MSSQL Management Studio
• Execute the following query on the source instance, replacing the parts in blue with the correct database name. Finally, execute the query.

DECLARE @sourceDB AS varchar(128) = 'sourceDatabaseName';
DECLARE @exportPath AS varchar(128) = '\\mySharedFolderPath\' + @sourceDB + '.bak'; 
BACKUP DATABASE @sourceDB TO DISK = @exportPath;

• Check the MSSQL query log to make sure there are no errors

Should you have an error message, this could be linked to the fact the MSSQL instance service account is not part of the authorized groups that can connect to the CIFS file share you set up.

2. Restoring the Dump to the Target Database

Connect to the target instance with a MSSQL Management Studio and open a new query. Copy / paste the code below replacing the parts in blue with the correct database names and execute the query.

DECLARE @SourceDB AS varchar(128) = 'sourceDatabaseName'; 
DECLARE @TargetDB AS varchar(128) = 'targetDatabaseName'; 
DECLARE @DumpPath AS varchar(128) = '\\mySharedFolderPath\'+@SourceDB+'.bak';
DECLARE @TargetDBData AS varchar(512); 
DECLARE @TargetDBLog AS varchar(512); 
SET @TargetDBData = (select physical_name from sys.master_files where database_id = (select database_id from sys.databases where name = @targetDB) and type_desc = 'ROWS'); 
SET @TargetDBLog = (select physical_name from sys.master_files where database_id = (select database_id from sys.databases where name = @targetDB) and type_desc = 'LOG'); 
print 'Target Data File Path : '+@TargetDBData; 
print 'Target Log File Path : '+@TargetDBLog;
CREATE TABLE #tmp (LogicalName nvarchar(128) NOT NULL, PhysicalName nvarchar(260) NOT NULL, Type char(1) NOT NULL, FileGroupName nvarchar(120) NULL, Size numeric(20, 0) NOT NULL, MaxSize numeric(20, 0) NOT NULL, FileID bigint NULL, CreateLSN numeric(25,0) NULL, DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier NULL, ReadOnlyLSN numeric(25,0) NULL , ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint NULL, SourceBlockSize int NULL, FileGroupID int NULL, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(25,0)NULL, DifferentialBaseGUID uniqueidentifier NULL, IsReadOnly bit NULL,
 IsPresent bit NULL, TDEThumbprint varbinary(32) NULL );
 INSERT #tmp EXEC ('restore filelistonly from disk = ''' + @DumpPath + '''')
 DECLARE @DataFileName AS varchar(128);DECLARE @LogFileName AS varchar(128);
 SET @DataFileName = (select logicalname from #tmp where type='D');SET @LogFileName = (select logicalname from #tmp where type='L');
 print 'Data File Name : '+@DataFileName;print 'Log File Name : '+@LogFileName;
 DROP TABLE #tmp
-- Set DB in single user to kill other connections and revert
USE master;
EXEC('ALTER DATABASE ' + @TargetDB+ ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
EXEC('ALTER DATABASE ' + @TargetDB+ ' SET MULTI_USER');
-- Start Restore process
RESTORE DATABASE @TargetDB FROM DISK = @DumpPath WITH REPLACE,MOVE @DataFileName TO @TargetDBData,MOVE @LogFileName TO @TargetDBLog

The script is designed to perform the following operations :

• Analyze source database dump file to identify data & log names
• Analyze target database structure to define the path to the MDF / LDF files that need to be replaced
• Restrict the target database to SINGLE_USER mode in order to kill all other connections
• Perform the refresh operation

Once the refresh has completed, you should get the following log : 

An updated version of this script should soon provide the missing step of the process : export account mappings and remap accounts after the refresh is complete...