Restore process automation

USE Master;
GO
SET NOCOUNT ON

— 1 – Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

— 2 – Initialize variables
SET @dbName = ‘Customer’
SET @backupPath = ‘D:\SQLBackups\’

— 3 – get list of files
SET @cmd = ‘DIR /b ‘ + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

— 4 – Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE ‘%.BAK’
AND backupFile LIKE @dbName + ‘%’

SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ FROM DISK = ”’
+ @backupPath + @lastFullBackup + ”’ WITH NORECOVERY, REPLACE’
PRINT @cmd

— 4 – Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE ‘%.DIF’
AND backupFile LIKE @dbName + ‘%’
AND backupFile > @lastFullBackup

— check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ FROM DISK = ”’
+ @backupPath + @lastDiffBackup + ”’ WITH NORECOVERY’
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END

— 5 – check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE ‘%.TRN’
AND backupFile LIKE @dbName + ‘%’
AND backupFile > @lastFullBackup

OPEN backupFiles

— Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘RESTORE LOG ‘ + @dbName + ‘ FROM DISK = ”’
+ @backupPath + @backupFile + ”’ WITH NORECOVERY’
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

— 6 – put database in a useable state
SET @cmd = ‘RESTORE DATABASE ‘ + @dbName + ‘ WITH RECOVERY’
PRINT @cmd

 

Please follow the below URL:

https://www.mssqltips.com/sqlservertip

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s