加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

SQLServer 生成还原数据库脚本的存储过程！

发布时间：2020-12-12 13:12:31 所属栏目：MsSql教程 来源：网络整理
导读：手动还原备份时，比较麻烦，尤其日志文件多的时候！由于国庆时不在，同事需要查询历史数据，需要还原数据库时不敢还原，才想到写这个脚本。脚本中会误差5分钟，如果有日志备份时间小于5分钟的可以改下脚本。该脚本适合有完整+差异+日志的备份。 存储过程提供

手动还原备份时，比较麻烦，尤其日志文件多的时候！由于国庆时不在，同事需要查询历史数据，需要还原数据库时不敢还原，才想到写这个脚本。脚本中会误差5分钟，如果有日志备份时间小于5分钟的可以改下脚本。该脚本适合有完整+差异+日志的备份。


存储过程提供个参数:


还原的数据库:@DatabaseNVARCHAR(128)

还原后的别名@RecoveryDBName)

恢复的时间点@RecoveryTimeDATETIME

恢复到的路径@RecoveryDBPath1000)


USE master
GO
/*
导出还原脚本 KK 2015-10-15

DECLARE 
 @Database NVARCHAR(128),@RecoveryDBName NVARCHAR(128),@RecoveryTime DATETIME,@RecoveryDBPath NVARCHAR(1000)

SET @Database = N'Demodb'
SET @RecoveryDBName = N'Demodb20151012'
SET @RecoveryTime = N'2015-09-29 10:35:00'
SET @RecoveryDBPath = N'G:DataBaseDemodb20151012'

EXEC DBO.OPT_GetDBRecoveryScript @Database,@RecoveryDBName,@RecoveryTime,@RecoveryDBPath

*/
ALTER PROCEDURE DBO.OPT_GetDBRecoveryScript(
	@Database NVARCHAR(128),@RecoveryDBPath NVARCHAR(1000)
)
AS
BEGIN
SET NOCOUNT ON
SET @Database = LTRIM(RTRIM(ISNULL(@Database,'')))
SET @RecoveryDBName = LTRIM(RTRIM(ISNULL(@RecoveryDBName,'')))
SET @RecoveryDBPath = LTRIM(RTRIM(ISNULL(@RecoveryDBPath,'')))

IF (@Database='' OR @RecoveryDBName='' OR @RecoveryDBPath='')
BEGIN
    PRINT N'输入不能为空！'
    RETURN -1
END

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.backupset 
    WHERE database_name = @Database AND backup_finish_date >= @RecoveryTime)
BEGIN
    PRINT N'该数据库当前时间无备份！'
    RETURN -1
END

IF EXISTS(SELECT * FROM Master.sys.sysdatabases WHERE name = @RecoveryDBName)
BEGIN
    PRINT N'还原的数据库已存在，不可覆盖！'
    RETURN -1
END

IF (RIGHT(@RecoveryDBPath,1)<>'')
    SET @RecoveryDBPath = @RecoveryDBPath + ''

/*DROP TABLE #FileExist*/
CREATE TABLE #FileExist(ExistFile BIT,ExistPath BIT,ExistRoot BIT)
INSERT INTO #FileExist EXEC master..xp_fileexist @RecoveryDBPath

IF NOT EXISTS(SELECT * FROM #FileExist WHERE ExistRoot=1 AND ExistPath=1)
BEGIN
    PRINT N'还原的路径不存在！'
    RETURN -1
END


/*利用最近的2次备份计算备份的间隔*/
DECLARE @FullBackupInterval INT
DECLARE @DiffBackupInterval INT
DECLARE @LogBackupInterval  INT

/*DROP TABLE #BackupDate*/
CREATE TABLE #BackupDate(type VARCHAR(1),FinishDate DATETIME,ID tinyint)

INSERT INTO #BackupDate(type,FinishDate,ID)
SELECT TOP(2) type,backup_finish_date,ROW_NUMBER()OVER(ORDER BY backup_finish_date DESC) ID
FROM msdb.dbo.backupset(NOLOCK)
WHERE database_name = @Database and type = 'D' 
ORDER BY backup_finish_date DESC

INSERT INTO #BackupDate(type,ROW_NUMBER()OVER(ORDER BY backup_finish_date DESC) ID
FROM msdb.dbo.backupset(NOLOCK)
WHERE database_name = @Database and type = 'I' 
ORDER BY backup_finish_date DESC

INSERT INTO #BackupDate(type,ROW_NUMBER()OVER(ORDER BY backup_finish_date DESC) ID
FROM msdb.dbo.backupset(NOLOCK)
WHERE database_name = @Database and type = 'L' 
ORDER BY backup_finish_date DESC

/*用于备份的间隔计算,增加5分钟减少误差*/
SELECT 
 @FullBackupInterval=DateDiff(SS,D2_FinishDate,D1_FinishDate)+300,@DiffBackupInterval=DateDiff(SS,I2_FinishDate,I1_FinishDate)+300,@LogBackupInterval=DateDiff(SS,L2_FinishDate,L1_FinishDate)+300
FROM(
	SELECT 
	MAX(CASE WHEN type='D' AND ID=1 THEN FinishDate ELSE '1900-01-01' END) D1_FinishDate,MAX(CASE WHEN type='D' AND ID=2 THEN FinishDate ELSE '1900-01-01' END) D2_FinishDate,MAX(CASE WHEN type='I' AND ID=1 THEN FinishDate ELSE '1900-01-01' END) I1_FinishDate,MAX(CASE WHEN type='I' AND ID=2 THEN FinishDate ELSE '1900-01-01' END) I2_FinishDate,MAX(CASE WHEN type='L' AND ID=1 THEN FinishDate ELSE '1900-01-01' END) L1_FinishDate,MAX(CASE WHEN type='L' AND ID=2 THEN FinishDate ELSE '1900-01-01' END) L2_FinishDate
	FROM #BackupDate
) AS TAB


/*考虑几种情况: 完整,完整+日志,完整+差异,完整+差异+日志 */
DECLARE @SCRIPT NVARCHAR(4000)
DECLARE @FullBackupDatetime Datetime
DECLARE @DiffBackupDatetime Datetime
DECLARE @LogBackupDatetime Datetime

/*取最接近还原时间的完整备份时间*/
SELECT @FullBackupDatetime=MAX(backup_finish_date) 
FROM msdb.dbo.backupset (NOLOCK)
WHERE database_name = @Database AND type = 'D'
AND backup_finish_date <= @RecoveryTime
AND backup_finish_date > DATEADD(SS,-@FullBackupInterval,@RecoveryTime)

SELECT @SCRIPT=physical_device_name 
FROM msdb.dbo.backupmediafamily (NOLOCK)
WHERE media_set_id IN(
	SELECT media_set_id 
	FROM msdb.dbo.backupset (NOLOCK)
	WHERE database_name = @Database AND type = 'D' 
	AND backup_finish_date = @FullBackupDatetime
)

SET @SCRIPT = 
  N'RESTORE DATABASE ['+@RecoveryDBName+N'] '+CHAR(10)
+ N'FROM  DISK = N'''+@SCRIPT+''' '+CHAR(10)
+ N'WITH  FILE = 1,'+CHAR(10)

SELECT @SCRIPT = @SCRIPT + N'MOVE N'''+name+N''' TO N'''
+@RecoveryDBPath+REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('',REVERSE(physical_name))-1))+''','+CHAR(10)
FROM sys.master_files 
WHERE database_id = DB_ID(@Database); 


/*还原时间是否刚好为完整备份时间,并输出脚本*/
IF EXISTS(SELECT 1 FROM msdb.dbo.backupset(NOLOCK) 
    WHERE database_name = @Database AND type = 'D' AND backup_finish_date = @RecoveryTime)
BEGIN
	SET @SCRIPT = @SCRIPT + N'RECOVERY,NOUNLOAD,STATS = 10 '+CHAR(10)+N'GO'+CHAR(10)
	PRINT '/*完整备份还原*/'
	PRINT @SCRIPT
	RETURN 0
END
ELSE
BEGIN
	SET @SCRIPT = @SCRIPT + N'NORECOVERY,STATS = 10 '+CHAR(10)+N'GO'+CHAR(10)
	PRINT '/*完整备份还原*/'
	PRINT @SCRIPT
END

SET @SCRIPT = ''

/*取最接近还原时间的差异备份时间*/
SELECT @DiffBackupDatetime=MAX(backup_finish_date) 
FROM msdb.dbo.backupset (NOLOCK)
WHERE database_name = @Database AND type = 'I'
AND backup_finish_date <= @RecoveryTime
AND backup_finish_date > DATEADD(SS,-@DiffBackupInterval,@RecoveryTime)
AND backup_finish_date > @FullBackupDatetime/*并大于完整备份时间*/

SELECT @SCRIPT=physical_device_name 
FROM msdb.dbo.backupmediafamily (NOLOCK)
WHERE media_set_id IN(
	SELECT media_set_id 
	FROM msdb.dbo.backupset (NOLOCK)
	WHERE database_name = @Database AND type = 'I' 
	AND backup_finish_date > @FullBackupDatetime
	AND backup_finish_date = @DiffBackupDatetime 
)


IF (ISNULL(@SCRIPT,'')<>'')
BEGIN
	PRINT '/*差异备份还原*/'
	/*还原时间是否刚好为差异备份时间,并输出脚本*/
	IF EXISTS(SELECT 1 FROM msdb.dbo.backupset(NOLOCK) 
	   WHERE database_name = @Database AND type = 'I' AND backup_finish_date = @RecoveryTime)
	BEGIN
		/*完整+差异*/
		SET @SCRIPT = N'RESTORE DATABASE ['+@RecoveryDBName+N'] '+CHAR(10)
		+N'FROM  DISK = N''' +@SCRIPT +N''''+CHAR(10)
		+N'WITH  FILE = 1,RECOVERY,STATS = 10 '+CHAR(10)
		+N'GO'+CHAR(10)
		PRINT @SCRIPT
		RETURN 0
	END
	ELSE
	BEGIN
		/*完整+差异+日志*/
		SET @SCRIPT = N'RESTORE DATABASE ['+@RecoveryDBName+N'] '+CHAR(10)
		+N'FROM  DISK = N''' +@SCRIPT +N''''+CHAR(10)
		+N'WITH  FILE = 1,NORECOVERY,STATS = 10 '+CHAR(10)
		+N'GO'+CHAR(10)
		PRINT @SCRIPT
	END
END
ELSE /*完整+日志*/
BEGIN
	SET @DiffBackupDatetime = @FullBackupDatetime
END

SET @SCRIPT = ''

/*取最接近还原时间的日志备份时间*/
SELECT @SCRIPT = @SCRIPT
+N'RESTORE LOG ['+@RecoveryDBName+N'] '+CHAR(10)
+N'FROM  DISK = N'''+physical_device_name+N''''+CHAR(10)
+N'WITH  FILE = 1,STATS = 10'+CHAR(10)
+N'GO'+CHAR(10)
 FROM msdb.dbo.backupmediafamily (NOLOCK)
WHERE media_set_id IN(
	SELECT media_set_id 
	FROM msdb.dbo.backupset (NOLOCK)
	WHERE database_name = @Database AND type = 'L' 
	AND backup_finish_date > @DiffBackupDatetime
	AND backup_finish_date < @RecoveryTime 
)

PRINT '/*日志备份还原*/'
PRINT @SCRIPT
SET @SCRIPT = ''

/*还原时间刚好为日志备份时间*/
IF EXISTS(SELECT 1 FROM msdb.dbo.backupset (NOLOCK) 
    WHERE database_name = @Database AND type = 'L' AND backup_finish_date = @RecoveryTime)
BEGIN
	SELECT @SCRIPT = 
	+N'RESTORE LOG ['+@RecoveryDBName+N'] '+CHAR(10)
	+N'FROM  DISK = N'''+physical_device_name+N''''+CHAR(10)
	+N'WITH  FILE = 1,STATS = 10'+CHAR(10)
	+N'GO'+CHAR(10)
	 FROM msdb.dbo.backupmediafamily (NOLOCK)
	WHERE media_set_id IN(
		SELECT media_set_id 
		FROM msdb.dbo.backupset (NOLOCK)
		WHERE database_name = @Database AND type = 'L' 
		AND backup_finish_date > @DiffBackupDatetime
		AND backup_finish_date = @RecoveryTime 
	)
	PRINT @SCRIPT
END
ELSE/*否则为时间点恢复*/
BEGIN
	/*取下一个日志的备份时间*/
	SELECT @LogBackupDatetime=MIN(backup_finish_date) 
	FROM msdb.dbo.backupset (NOLOCK)
	WHERE database_name = @Database AND type = 'L'
	AND backup_finish_date < DATEADD(SS,@LogBackupInterval,@RecoveryTime)
	AND backup_finish_date > @RecoveryTime
	AND backup_finish_date > @DiffBackupDatetime/*并大于差异备份时间*/

	SELECT @SCRIPT = 
	+N'RESTORE LOG ['+@RecoveryDBName+N'] '+CHAR(10)
	+N'FROM  DISK = N'''+physical_device_name+N''''+CHAR(10)
	+N'WITH  FILE = 1,STATS = 10,STOPAT = N'''
	+CONVERT(varchar(30),126)+''''+CHAR(10)
	+N'GO'+CHAR(10)
	 FROM msdb.dbo.backupmediafamily (NOLOCK)
	WHERE media_set_id IN(
		SELECT media_set_id 
		FROM msdb.dbo.backupset (NOLOCK)
		WHERE database_name = @Database AND type = 'L' 
		AND backup_finish_date = @LogBackupDatetime 
	)
	PRINT @SCRIPT
END

SET NOCOUNT OFF
RETURN 0
END
GO


示例：

DECLARE 
 @Database NVARCHAR(128),@RecoveryDBPath NVARCHAR(1000)
SET @Database = N'SourseDB'
SET @RecoveryDBName = N'RecoDB'
SET @RecoveryTime = N'2015-10-2 5:00:00'
SET @RecoveryDBPath = N'G:数据库备份'
EXEC DBO.OPT_GetDBRecoveryScript @Database,@RecoveryDBPath

/*完整备份还原*/
RESTORE DATABASE [RecoDB] 
FROM  DISK = N'F:数据库自动备份完全备份SourseDBSourseDB_backup_2015_09_27_003001_9737603.bak' 
WITH  FILE = 1,MOVE N'SourseDB' TO N'G:数据库备份SourseDB.mdf',MOVE N'SourseDB_log' TO N'G:数据库备份SourseDB_log.ldf',MOVE N'file_1' TO N'G:数据库备份file_1.ndf',MOVE N'file_2' TO N'G:数据库备份file_2.ndf',MOVE N'file_3' TO N'G:数据库备份file_3.ndf',MOVE N'file_4' TO N'G:数据库备份file_4.ndf',MOVE N'file_5' TO N'G:数据库备份file_5.ndf',MOVE N'file_6' TO N'G:数据库备份file_6.ndf',MOVE N'file_7' TO N'G:数据库备份file_7.ndf',MOVE N'file_8' TO N'G:数据库备份file_8.ndf',MOVE N'file_9' TO N'G:数据库备份file_9.ndf',MOVE N'file_10' TO N'G:数据库备份file_10.ndf',MOVE N'file_11' TO N'G:数据库备份file_11.ndf',MOVE N'file_12' TO N'G:数据库备份file_12.ndf',STATS = 10 
GO

/*差异备份还原*/
RESTORE DATABASE [RecoDB] 
FROM  DISK = N'F:数据库自动备份差异备份SourseDBSourseDB_backup_2015_10_02_000002_1624226.bak'
WITH  FILE = 1,STATS = 10 
GO

/*日志备份还原*/
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:数据库自动备份日志备份SourseDBSourseDB_backup_2015_10_02_010001_9672276.trn'
WITH  FILE = 1,STATS = 10
GO
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:数据库自动备份日志备份SourseDBSourseDB_backup_2015_10_02_020002_2641026.trn'
WITH  FILE = 1,STATS = 10
GO
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:数据库自动备份日志备份SourseDBSourseDB_backup_2015_10_02_030001_7328526.trn'
WITH  FILE = 1,STATS = 10
GO
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:数据库自动备份日志备份SourseDBSourseDB_backup_2015_10_02_040001_9203526.trn'
WITH  FILE = 1,STATS = 10
GO

RESTORE LOG [RecoDB] 
FROM  DISK = N'F:数据库自动备份日志备份SourseDBSourseDB_backup_2015_10_02_050001_6078526.trn'
WITH  FILE = 1,STOPAT = N'2015-10-02T05:00:00'
GO


Hello.KK （SQL Server）:??http://blog.csdn.net/kk185800961/article/details/49154637

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同