MSSQL通用还原脚本

32饿312

SQL Server通用还原脚本，只需修改第二步中，需要还原的数据库名称和路径

执行完脚本后会生成对应的还原命令，直接新建查询后执行即可

-- 2 - Initialize variables

SET @dbName = 'Customer'

SET @backupPath = 'D:\SQLBackups\'　

脚本如下：

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

   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