Restoring User Databases to Different Environments

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Database Administration - Backup & Recovery

Contents

Restoring User Databases to a Different Environment

Any task that you have to do more then once is a great candidate for some type of automation to help make your job that much easier. One request that I get many times is about refreshing DEV or UAT from a copy of the most recent PROD backup.

Now depending on the number of data and log files and your backup configuration it might be a bit of a challenge to write a script from scratch and if you're like me you try and avoid the GUI as much as possible.

USE CASE

I want to refresh my DEV database from last night's PROD backup.

Variables

  • SourceDatabaseName - This is the name of your production database
  • TargetDatabaseName - This is the name of your development database that you're looking to refresh
  • DestinationDataDrive - This is the location where you want your data files to be restored to
  • DestinationLogDrive - This is the location where you want your log files to be restored to
  • DestinationFTDrive - This is the location where you want your FT data to be stored


This is a "version 1" script so I'm hoping with all of the great SQLServerPedia contributors out there that this script can be tweaked and used as a building block to create a solution that would work for everyone.


T-SQL Code

DECLARE @RestoreSQLStmt nvarchar(2000)

DECLARE @SourceDatabaseName nvarchar(100)
DECLARE @TargetDatabaseName nvarchar(100)

DECLARE @File_Number int
DECLARE @File_Type nvarchar(10)
DECLARE @logical_name nvarchar(255)

DECLARE @DestinationDataDrive nvarchar(5)
DECLARE @DestinationLogDrive nvarchar(5)
DECLARE @DestinationFTDrive nvarchar(10)
DECLARE @position varchar(5)

DECLARE @BackupFileCount int
DECLARE @RestoreFileCounter int

SET @SourceDatabaseName = 'TestDBProd01'
SET @TargetDatabaseName = 'TestDBDev01'

SET @DestinationDataDrive = 'F:\'
SET @DestinationLogDrive = 'G:\'
SET @DestinationFTDrive = @DestinationDataDrive + 'FTData\'

SET @RestoreFileCounter = 0
		
SELECT  @BackupFileCount = count(B.physical_device_name)
FROM	msdb.dbo.backupmediafamily B
WHERE	media_set_id = (SELECT  A.media_set_id
						FROM	msdb.dbo.backupset A
						WHERE	A.backup_set_id = (	SELECT	MAX(backup_set_id)
										FROM	msdb.dbo.backupset
										WHERE	database_name = @SourceDatabaseName AND
											type = 'D'))

SELECT @Position = (	SELECT	A.position
						FROM	msdb.dbo.backupset A
						WHERE	A.backup_set_id = (	SELECT	MAX(backup_set_id)
										FROM	msdb.dbo.backupset
										WHERE	database_name = @SourceDatabaseName AND
											type = 'D'))


SELECT  @RestoreSQLStmt = 'RESTORE DATABASE ' + @TargetDatabaseName + ' FROM ' 
		
WHILE @RestoreFileCounter <> @BackupFileCount
BEGIN
		SELECT @RestoreFileCounter = @RestoreFileCounter + 1
		SELECT @RestoreSQLStmt = @RestoreSQLStmt + 'DISK = N' + CHAR(39) +
				(SELECT B.physical_device_name + CHAR(39) + ','
				FROM	msdb.dbo.backupmediafamily B
				WHERE	B.family_sequence_number = @RestoreFileCounter AND
						media_set_id = (	SELECT  A.media_set_id
										FROM	msdb.dbo.backupset A
										WHERE	A.backup_set_id = (
											SELECT	MAX(backup_set_id)
											FROM	msdb.dbo.backupset
											WHERE	database_name = @SourceDatabaseName AND
													type = 'D')))
END

SELECT @RestoreSQLStmt = LEFT(@RestoreSQLStmt, LEN(@RestoreSQLStmt)-1) + ' WITH FILE = ' + @Position + ', '

Select	file_number, file_type, logical_name
INTO	#tmpDatabaseFiles
from	msdb.dbo.backupfile
WHERE	Backup_Set_id = (SELECT	MAX(backup_set_id)
FROM	msdb.dbo.backupset
WHERE	database_name = @SourceDatabaseName AND
		type = 'D')

DECLARE curTmpDatbaseFiles CURSOR FOR
SELECT	file_number, file_type, logical_name
FROM	#tmpDatabaseFiles


OPEN curTmpDatbaseFiles

	FETCH NEXT FROM curTmpDatbaseFiles
	INTO @file_number, @file_type, @logical_name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF		@file_type = 'D'
			BEGIN
				SELECT @RestoreSQLStmt = @RestoreSQLStmt + ' MOVE N' + CHAR(39) + @logical_name + 
				CHAR(39) + ' TO ' +  'N' + CHAR(39) + @DestinationDataDrive + @SourceDatabaseName + '_' + 
				convert(nvarchar(2), @file_number) + '.MDF' + CHAR(39) + ','
			END
		IF		@file_type = 'L'
			BEGIN
				SELECT @RestoreSQLStmt = @RestoreSQLStmt + ' MOVE N' + CHAR(39) + @logical_name + 
				CHAR(39) + ' TO ' +  'N' + CHAR(39) + @DestinationLogDrive + @SourceDatabaseName + '_' + 
				convert(nvarchar(2), @file_number) + '.LDF' + CHAR(39)+ ','
		END
		IF		@file_type = 'F'
			BEGIN
				SELECT @RestoreSQLStmt = @RestoreSQLStmt + ' MOVE N' + CHAR(39) + @logical_name + 
				CHAR(39) + ' TO ' +  'N' + CHAR(39) + @DestinationFTDrive + @SourceDatabaseName + '_' + 
				convert(nvarchar(10), @file_number) + '.FTDATA' + CHAR(39) + ','
		END
		
		FETCH NEXT FROM curTmpDatbaseFiles
		INTO @file_number, @file_type, @logical_name
	END
	
CLOSE curTmpDatbaseFiles
DEALLOCATE curTmpDatbaseFiles

SELECT @RestoreSQLStmt = @RestoreSQLStmt + ' NOUNLOAD, STATS = 10'

SELECT @RestoreSQLStmt

DROP TABLE #tmpDatabaseFiles

Related Reading

Author Credits

Colin Stasiuk

Colin Stasiuk, Senior SQL Server DBA, owner of Benchmark IT Consulting, an Edmonton-based consulting company. He has worked with SQL Server since 1996 and has earned the Microsoft Certified IT Professional (MCITP), Microsoft Certified Technology Specialist(MCTS), and the Microsoft Certified Professional (MCP) accreditations. Colin is also the president of EDMPASS (The Edmonton based chapter of PASS)and is an avid hockey fan (as any good Canadian boy should be)

Colin can be reached at: ColinStasiuk@BenchmarkITConsulting.com .

His online presence includes:

Retrieved from "http://sqlserverpedia.com/wiki/Restoring_User_Databases_to_Different_Environments"