sqlserver中指定时间还原数据库的存储过程
USE [master]
GO
/****** Object: StoredProcedure [dbo].[pr_DBA_RestoreTimeDatabase] Script Date: 01/10/2018 11:29:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <echo>
— Create date: <2016-04-28>
— Description: <还原trn>
— =============================================
ALTER PROCEDURE [dbo].[pr_DBA_RestoreTimeDatabase]
@dbName VARCHAR(100),
@Time DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @IP VARCHAR(100)
declare @time1 nvarchar(50)
DECLARE @dbfile VARCHAR(200)
DECLARE @SQL VARCHAR(2000)
–获取数据库的IP（这个地方是通过之前创建的统计所有数据库信息的一张表来实现的）
SELECT @IP=IP FROM BeisenStatistics.dbo.tbl_MachineDbName where DBName=@dbName
–print @ip
–拼接数据库备份的文件名
select @dbfile=@dbName+CONVERT(varchar(10),@Time,112)+’.day’
–print @dbfile
–建立互信
SET @SQL = N”
set @SQL = ‘
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N”’+@dbName+””
–print @sql
exec(@sql)
SET @SQL = ‘xp_cmdshell ”net use ‘+’\\ip\db_backup\’+@IP+’ “密码” /user:prod\pro”’
–print @sql
EXEC(@SQL)
SET @SQL = ‘xp_cmdshell ”net use \\ip\AllDbStandbyLog “密码” /user:prod\pro”’
–print @sql
EXEC(@SQL)
–robocopy拷贝备份文件
set @SQL= ‘xp_cmdshell ”Robocopy.exe \\1ip\db_backup\’+@IP+’\ E:\cyt ‘+@dbfile+’ /ipg:30”’
–print @sql
exec(@sql)
–还原备份
set @SQL = ‘RESTORE DATABASE ‘+@dbName+’ FROM DISK = N”E:\cyt\’+@dbfile+”’ WITH FILE = 1, MOVE N”’+@dbName+”’ TO N”F:\datafile\’+@dbName+’_1.mdf”, MOVE N”’+@dbName+’_log” TO N”F:\logfile\’+@dbName+’_1.ldf”, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10′
–print @sql
exec(@sql)
—-将所有数据库trn文件导入临时表alltrn
–drop table #alltrn
CREATE TABLE #alltrn(name VARCHAR(500),filetime as stuff(STUFF(name,1,charindex(‘__’,name)+1,”),charindex(‘.’,STUFF(name,1,charindex(‘__’,name)+1,”)),4,”))
SET @SQL = ‘xp_cmdshell ”dir \\ip\AllDbStandbyLog\’+@dbName+’/b /o:d”’
–print @sql
INSERT INTO #alltrn
EXEC (@SQL)
–select * from #alltrn
–获取数据库备份的还原时间
DECLARE @date DATETIME
SELECT @date = MAX(backup_finish_date) FROM msdb.dbo.backupset where database_name=@dbName
–数据库需要的trn文件信息导入临时表trnfile
–drop table #trnfile
select * into #trnfile from #alltrn where dbo.fn_timestamp_to_datetime(filetime) >@date and dbo.fn_timestamp_to_datetime(filetime) <= DATEADD(N,15,@Time) and name is not null
–select * from #trnfile
–将数据库库需要的trn文件拷贝到本地
DECLARE @name NVARCHAR(200)
SET @name = N”
DECLARE cur CURSOR FOR
SELECT name from #trnfile where name IS NOT NULL
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL = ‘xp_cmdshell ”copy /Y \\ip\AllDbStandbyLog\’+@dbName+’\’+@name+ ‘ E:\cyt\trn\’+@name+””
–PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM cur into @name
END
CLOSE cur
DEALLOCATE cur
–还原数据库日志文件
— SELECT name FROM #trnfile
–WHERE dbo.fn_timestamp_to_datetime(filetime) >@date and dbo.fn_timestamp_to_datetime(filetime) < @Time
–ORDER BY dbo.fn_timestamp_to_datetime(filetime) ASC
DECLARE @noRestoredTrn VARCHAR(200)
DECLARE my_cursor1 CURSOR FOR
SELECT name FROM #trnfile
WHERE dbo.fn_timestamp_to_datetime(filetime) >@date and dbo.fn_timestamp_to_datetime(filetime) < @time
ORDER BY dbo.fn_timestamp_to_datetime(filetime) ASC
OPEN my_cursor1
FETCH NEXT FROM my_cursor1 INTO @noRestoredTrn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘RESTORE LOG [‘+@dbName+’] FROM DISK = N”E:\cyt\trn\’+@noRestoredTrn+”’ WITH norecovery ,replace’
–PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM my_cursor1 INTO @noRestoredTrn
END
CLOSE my_cursor1
DEALLOCATE my_cursor1
declare @trn varchar(200)
SELECT @trn= name FROM #trnfile
WHERE dbo.fn_timestamp_to_datetime(filetime) >= @time
–select @trn
set @sql = ‘RESTORE LOG [‘+@dbName+’] FROM DISK = N”E:\cyt\trn\’+@trn+”’ WITH FILE = 1, KEEP_REPLICATION, NOUNLOAD, STATS = 10, STOPAT = N”’+convert(varchar(20),@Time,126)+””
–print @sql
exec(@sql)
SET @SQL = ‘xp_cmdshell ”forfiles /p ‘+’E:\cyt\’+’ /m *.day /s /c “cmd /c del @file””’
–PRINT @SQL
EXEC(@SQL)
SET @SQL = ‘xp_cmdshell ”forfiles /p ‘+’E:\cyt\trn\’+’ /m *.* /s /c “cmd /c del @file””’
–PRINT @SQL
EXEC(@SQL)
END