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

最新推荐文章于 2024-06-07 11:04:18 发布

薛定谔的DBA

最新推荐文章于 2024-06-07 11:04:18 发布

阅读量3.6k

点赞数

分类专栏： SQLServer DBA SQLServer

本文链接：https://blog.csdn.net/kk185800961/article/details/49154637

版权

SQLServer 同时被 2 个专栏收录

269 篇文章 25 订阅

订阅专栏

SQLServer DBA

33 篇文章 7 订阅

订阅专栏

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

存储过程提供个参数:

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

还原后的别名:@RecoveryDBNameNVARCHAR(128)

恢复的时间点:@RecoveryTimeDATETIME

恢复到的路径:@RecoveryDBPathNVARCHAR(1000)

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:\DataBase\Demodb20151012\'
EXEC DBO.OPT_GetDBRecoveryScript @Database,@RecoveryDBName,@RecoveryTime,@RecoveryDBPath
*/
CREATE PROCEDURE DBO.OPT_GetDBRecoveryScript(
	@Database NVARCHAR(128)
    , @RecoveryDBName NVARCHAR(128)
    , @RecoveryTime DATETIME
    , @RecoveryDBPath NVARCHAR(1000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DatetimeStr NVARCHAR(30)
SET @DatetimeStr = CONVERT(varchar(30),GETDATE(),112)+REPLACE(CONVERT(varchar(30),GETDATE(),24),':','')

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,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 = 'I' 
ORDER BY backup_finish_date DESC
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 = '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+REPLACE(REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))-1)),'.',@DatetimeStr+'.')+''','+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,  NOUNLOAD,  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,  NOUNLOAD,  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,  NOUNLOAD,  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,  NORECOVERY,  NOUNLOAD,  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,  RECOVERY,  NOUNLOAD,  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, RECOVERY, NOUNLOAD, STATS = 10 , STOPAT = N'''
	+CONVERT(varchar(30),@RecoveryTime,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

示例：

use master
GO
EXEC DBO.OPT_GetDBRecoveryScript
 @Database=N'SourseDB'
,@RecoveryDBName=N'RecoDB'
,@RecoveryTime=N'2015-10-2 05:00:00'
,@RecoveryDBPath =N'G:\MSSQL\DATA\'
GO

以下都是执行存储过程后输出的全部信息：

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

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

/*日志备份还原*/
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_010001_9672276.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_020002_2641026.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_030001_7328526.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [RecoDB] 
FROM  DISK = N'F:\数据库自动备份\日志备份\SourseDB\SourseDB_backup_2015_10_02_040001_9203526.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

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

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

薛定谔的DBA

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQLServer 生成还原数据库脚本的存储过程！

手动还原备份时，比较麻烦，尤其日志文件多的时候！由于国庆时不在，同事需要查询历史数据，需要还原数据库时不敢还原，才想到写这个脚本。脚本中会误差5分钟，如果有日志备份时间小于5分钟的可以改下脚本。该脚本适合有完整+差异+日志的备份。存储过程提供个参数: 还原的数据库:@DatabaseNVARCHAR(128)还原后的别名:@RecoveryDBNameNVARCHAR(...
复制链接

扫一扫

专栏目录