如何实现alwayson的备份还原脚本

这篇文章主要介绍如何实现alwayson的备份还原脚本，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！
1、 备份数据库
在主副本上，将需要做AlwaysOn的数据库做一次全备和日志备份（NOTE：禁用事务日志备份作业，如果有的话）
替换参数，执行如下脚本生成备份语句，然后执行：
DECLARE @DBName
NVARCHAR(255)
DECLARE @SQL
NVARCHAR(MAX)
DECLARE @BackupToPath
NVARCHAR(500)
SET @DBName=’datayesdb’ –数据库名称
SET @BackupToPath=’D:’ –数据库备份在主副本的存放路径
SET NOCOUNT
ON
PRINT ‘– =============================================’
PRINT ‘–
AlwaysOn主副本上备份数据库(完整备份+事务日志备份)’+CHAR(13)
SET
@SQL=’USE [master]
GO
ALTER DATABASE [‘+@DBName+’] SET RECOVERY FULL;
GO
BACKUP DATABASE [‘+@DBName+’]
TO DISK=”’+@BackupToPath+”+@DBName+’.bak” WITH
COMPRESSION
GO
BACKUP LOG [‘+@DBName+’]
TO DISK=”’+@BackupToPath+”+@DBName+’.trn” WITH
COMPRESSION
GO’+CHAR(13)
PRINT @SQL
2、 还原数据库
将备份文件复制到辅助副本服务器，使用NORECOVERY方式还原。
替换参数，执行如下脚本生成备份语句，然后执行：
DECLARE @DBName
NVARCHAR(255)
DECLARE @SQL
NVARCHAR(MAX)
DECLARE @RestoreFromPath
NVARCHAR(MAX)
DECLARE @RestoreToDataFileFolder
NVARCHAR(200)
DECLARE @RestoreToLogFileFolder
NVARCHAR(200)
SET @DBName=’datayesdb’ –数据库名称
SET @RestoreFromPath=’D:share’ –数据库备份在辅助副本的存放路径
SET @RestoreToDataFileFolder=’D:SQLData’ –数据库备份的数据文件在辅助副本的还原路径
SET @RestoreToLogFileFolder=’D:SQLLog’ –数据库备份的日志文件在辅助副本的还原路径
SET NOCOUNT
ON
PRINT ‘–
=============================================’
PRINT ‘–
AlwayOn辅助副本还原数据库(指定NORECOVERY方式还原)’+CHAR(13)
DECLARE @RestoreFilePath
NVARCHAR(MAX)
DECLARE @LNAME
NVARCHAR(500)
DECLARE @PNAME
NVARCHAR(500)
DECLARE @PFName
NVARCHAR(500)
DECLARE @BackupType
CHAR(1)
SET @RestoreFilePath=”
SET @SQL
= ‘RESTORE FILELISTONLY
FROM DISK = ”’+@RestoreFromPath+”+@DBName+’.bak’+””
if OBJECT_ID (‘tempdb..#temp’)is not null
BEGIN
DROP
TABLE #BackupFileList
END
CREATE TABLE
#BackupFileList
(
LogicalName
NVARCHAR(128) ,
PhysicalName
NVARCHAR(260) ,
BackupType
CHAR(1) ,
FileGroupName
NVARCHAR(128) ,
SIZE
NUMERIC(20,0),
MaxSize
NUMERIC(20,0) ,
FileID
BIGINT ,
CreateLSN
NUMERIC(25,0) ,
DropLSN
NUMERIC(25,0) NULL ,
UniqueID
UNIQUEIDENTIFIER ,
ReadOnlyLSN
NUMERIC(25,0) NULL ,
ReadWriteLSN
NUMERIC(25,0) NULL ,
BackupSizeInBytes
BIGINT ,
SourceBlockSize
INT ,
FileGroupID
INT ,
LogGroupGUID
UNIQUEIDENTIFIER NULL
,
DifferentialBaseLSN
NUMERIC(25,0) NULL ,
DifferentialBaseGUID
UNIQUEIDENTIFIER ,
IsReadOnly
BIT ,
IsPresent
BIT ,
TDEThumbprint
NVARCHAR(100)
)
INSERT INTO
#BackupFileList EXEC (@SQL);
DECLARE CurTBName
CURSOR
FOR
SELECT
LogicalName,PhysicalName,BackupType FROM #BackupFileList
OPEN CurTBName
FETCH NEXT
FROM CurTBName INTO @LNAME,免费云主机域名@PNAME,@BackupType
WHILE @@FETCH_STATUS
= 0
BEGIN
SELECT
@PFName=RIGHT(@PNAME, CHARINDEX(”,REVERSE(@PNAME))-1)
SET @RestoreFilePath=’ MOVE N”’+@LNAME+”’ TO N”’
+CASE WHEN @BackupType=’D’ THEN @RestoreToDataFileFolder ELSE
@RestoreToLogFileFolder END
+”+@PFName+”’, ‘+CHAR(13)+@RestoreFilePath
FETCH
NEXT FROM CurTBName INTO @LNAME,@PNAME,@BackupType
END
CLOSE CurTBName
DEALLOCATE CurTBName
SET
@SQL=’USE [master]
GO
RESTORE DATABASE ‘+@DBName+’ FROM DISK = N”’+@RestoreFromPath+”+@DBName+’.bak” WITH FILE =
1,’+CHAR(13)
+@RestoreFilePath
+’NORECOVERY,NOUNLOAD,STATS
= 10
GO
RESTORE LOG ‘+@DBName+’ FROM DISK = N”’+@RestoreFromPath+”+@DBName+’.trn” WITH
NORECOVERY
GO’+CHAR(13)
PRINT @SQL
DROP TABLE
#BackupFileList以上是“如何实现alwayson的备份还原脚本”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注百云行业资讯频道！

相关推荐: 数据库中AGStatus SQL的示例代码

小编给大家分享一下数据库中AGStatus SQL的示例代码，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ select n.group_name,n.node_name,CASE …