MSSQL Universal Restore Script

MSSQL Restore Script

SQL Server Universal restore script, just modify the database name and path that you need to restore in the second step

After executing the script, a corresponding restore command is generated, which is executed directly after creating a new query.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/87/F7/wKiom1fk3pbCJjpdAAGIDH1JlH8572.png-wh_500x0-wm_3 -wmp_4-s_832388877.png "title=" qq picture 20160923154733.png "alt=" Wkiom1fk3pbcjjpdaagidh1jlh8572.png-wh_50 "/>

--2-initialize variables

SET @dbName = ' Customer '

SET @backupPath = ' D:\SQLBackups\ '　

The script is as follows:

--open--xp_cmdshell

sp_configure ' show advanced options ', 1

Reconfigure

Go

sp_configure ' xp_cmdshell ', 1

Reconfigure

Go

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 + '% '

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 + '% '

BackupFile > @lastFullBackup

OPEN Backupfiles

--Loop through all the files for the database

FETCH NEXT from Backupfiles to @backupFile

While @ @FETCH_STATUS = 0

BEGIN

SET @cmd = ' RESTORE LOG ' + @dbName + ' from DISK = '

+ @backupPath + @backupFile + ' with NORECOVERY '

PRINT @cmd

FETCH NEXT from Backupfiles to @backupFile

END

CLOSE Backupfiles

Deallocate backupfiles

--6-put database in a useable state

SET @cmd = ' RESTORE DATABASE ' + @dbName + ' with RECOVERY '

PRINT @cmd

This article is from the "Computer Network Technology" blog, please be sure to keep this source http://370220760.blog.51cto.com/6469179/1855856

MSSQL Universal Restore Script