Restore Backup and all TRANLOGS in a specific DIR

,

Many DBA's have a Maintenance plan running that creates a backup every night and a transaction log backup every hour.

Restoring them to another server (for whatever reason) can be a tedious job. That is why I wrote this T-SQL script.

It will read the contents of a backup directory created by the maintenance plan and create a script needed to restore the entire database.

Have fun with it,
and if you like it vote for it......


SET NOCOUNT ON
DECLARE @BACKUPDIR AS NVARCHAR(255) 	-- THE DIRECTORY WHERE THE ACKUPS ARE PLACED
DECLARE @RESTORESET AS INTEGER		-- THE BACKUPSET YOU WANT TO RESTORE
DECLARE @NEWDBNAME AS VARCHAR(200)	-- THE NEW DATABASE NAME
DECLARE @ORIGDBNAME AS VARCHAR(200) 	-- THE ORIGINAL DATABASE NAME
DECLARE @ORIGLGNAME AS VARCHAR(200) 	-- THE ORIGINAL LOG FILE NAME
DECLARE @NEWDEST AS VARCHAR(255)		-- THE NEW DESTINATION DIRECTORY

SET @BACKUPDIR  = 'F:\MSSQL\MSSQL\BACKUP\NORTHWIND\'
SET @RESTORESET = 2			-- RESTORE THE SECOND BACKUPSET FOUND IN THE DIRECTORY
SET @NEWDBNAME  = 'NORTHWIND'
SET @ORIGDBNAME = 'NORTHWIND'
SET @ORIGLGNAME = 'NORTHWIND_LOG'
SET @NEWDEST    = 'F:\MSSQL\MSSQL\DATA\'

DECLARE @DB_FILENAME_PRFX AS VARCHAR(200)
DECLARE @LG_FILENAME_PRFX AS VARCHAR(200)

CREATE TABLE #DIRECTORYTABLE 
( ID INT IDENTITY,
  SUBDIRECTORY SYSNAME,
  DEPTH INTEGER,
  [FILE] INTEGER,
  SORTNAME VARCHAR(200),
  SORTTIMESTR VARCHAR(30),
  SORTTIME DATETIME)

-- GET THE DIRECTORY INFORMATION
INSERT 	#DIRECTORYTABLE (SUBDIRECTORY,DEPTH,[FILE]) 
EXECUTE 	MASTER.DBO.XP_DIRTREE @BACKUPDIR, 1, 1

-- GET THE FILE NAMES
SELECT TOP 1 @DB_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'') 
FROM #DIRECTORYTABLE 
WHERE	SUBDIRECTORY LIKE '%.BAK'
SELECT TOP 1 @LG_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'') 
FROM 	#DIRECTORYTABLE 
WHERE	SUBDIRECTORY LIKE '%.TRN'


-- GET THE TIMESTRING COINTAINED IN THE FILENAME AND UPDATE THE TABLE
UPDATE #DIRECTORYTABLE 
SET 	SORTNAME = @DB_FILENAME_PRFX ,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@DB_FILENAME_PRFX,''),'.BAK','')
WHERE SUBDIRECTORY LIKE @DB_FILENAME_PRFX+'%' 

UPDATE #DIRECTORYTABLE 
SET SORTNAME = @LG_FILENAME_PRFX	,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@LG_FILENAME_PRFX,''),'.TRN','')
WHERE SUBDIRECTORY LIKE @LG_FILENAME_PRFX+'%' 

-- NOW CREATE A MEMORYTABLE AND FORMAT THE DATA AS NEEDED
DECLARE @MEMDIR TABLE (ID INT IDENTITY,[FILENAME] VARCHAR(200),SORTNAME VARCHAR(200),SORTTIMESTR CHAR(12),BACKUPSET INTEGER)

-- INSERT THE NEEDED VALUES IN THE CORRECT ORDER
INSERT @MEMDIR ([FILENAME],SORTNAME,SORTTIMESTR) SELECT SUBDIRECTORY,SORTNAME,SORTTIMESTR FROM #DIRECTORYTABLE ORDER BY SORTTIMESTR

-- DROP THE TEMP TABLE
DROP TABLE #DIRECTORYTABLE

-- DECLARE SOME OTHER NEEDED VARIABLES
DECLARE @NAME VARCHAR(200)
DECLARE @CURRENT INTEGER
DECLARE @MAX INTEGER
DECLARE @BACKUPSET INTEGER
DECLARE @CURRENTSET INTEGER
DECLARE @BCKFNAME VARCHAR(200)
DECLARE @MAXID INTEGER


-- ENUMERATE THE BACKUP SET AND DETERMINE HOW MANY SETS THERE ARE AND NUMBER THEM
SET @CURRENT = 1
SET @BACKUPSET = 0
SELECT @MAX = MAX(ID) FROM @MEMDIR
WHILE @CURRENT <= @MAX
BEGIN
	SELECT @NAME=SORTNAME FROM @MEMDIR WHERE ID = @CURRENT
	IF @NAME = @DB_FILENAME_PRFX
   BEGIN
      SET @BACKUPSET = @BACKUPSET + 1
   END
	UPDATE @MEMDIR SET BACKUPSET = @BACKUPSET WHERE ID = @CURRENT
	SET @CURRENT = @CURRENT + 1
END


-- OK WE KNOW WHICH BACKUP TO RESTORE ENUMERATE THE BACKUPS AGAIN AND OUTPUT THE RESTORE STATEMENTS
SELECT @MAXID = MAX(ID) FROM @MEMDIR WHERE BACKUPSET = @RESTORESET
SET @CURRENT = 1
WHILE @CURRENT <= @MAX
	BEGIN
		SELECT @NAME=SORTNAME,@CURRENTSET=BACKUPSET,@BCKFNAME= [FILENAME] FROM @MEMDIR WHERE ID = @CURRENT
		IF @CURRENTSET = @RESTORESET
			BEGIN
				IF @NAME = @DB_FILENAME_PRFX 
					BEGIN
						PRINT 'RESTORE DATABASE [' + @NEWDBNAME + ']'
						PRINT 'FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
						PRINT 'WITH MOVE ''' + @ORIGDBNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '.MDF'','
						PRINT '     MOVE ''' + @ORIGLGNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '_LOG.LDF'','
						IF @CURRENT < @MAXID PRINT '     NORECOVERY' 
						IF @CURRENT = @MAXID PRINT '     RECOVERY' 
					END
				ELSE
					BEGIN
						PRINT 'RESTORE LOG [' + @NEWDBNAME + ']'
						PRINT '     FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
						IF @CURRENT < @MAXID PRINT '     WITH NORECOVERY' 
						IF @CURRENT = @MAXID PRINT '     WITH RECOVERY -- STOPAT = ''YYYY-MM-DD UU:MM:SS.000'''
					END
			END
		SET @CURRENT = @CURRENT + 1
	END

Rate

5 (1)

Share

Share

Rate

5 (1)