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

这篇文章主要介绍如何实现alwayson的备份还原脚本，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

1、 备份数据库

在主副本上，将需要做AlwaysOn的数据库做一次全备和日志备份（NOTE：禁用事务日志备份作业，如果有的话）

替换参数，执行如下脚本生成备份语句，然后执行：

DECLARE @DBNameNVARCHAR(255)

DECLARE @SQLNVARCHAR(MAX)

DECLARE @BackupToPathNVARCHAR(500)

SET @DBName='datayesdb'                       --数据库名称

SET @BackupToPath='D:'                        --数据库备份在主副本的存放路径

SET NOCOUNTON

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'' WITHCOMPRESSION

GO

BACKUP LOG ['+@DBName+']

TO DISK='''+@BackupToPath+'\'+@DBName+'.trn'' WITHCOMPRESSION

GO'+CHAR(13)

PRINT @SQL

2、 还原数据库

将备份文件复制到辅助副本服务器，使用NORECOVERY方式还原。

替换参数，执行如下脚本生成备份语句，然后执行：

DECLARE @DBNameNVARCHAR(255)

DECLARE @SQLNVARCHAR(MAX)

DECLARE @RestoreFromPathNVARCHAR(MAX)

DECLARE @RestoreToDataFileFolderNVARCHAR(200)

DECLARE @RestoreToLogFileFolderNVARCHAR(200)

SET @DBName='datayesdb'                       --数据库名称

SET @RestoreFromPath='D:\share'               --数据库备份在辅助副本的存放路径

SET @RestoreToDataFileFolder='D:\SQLData'     --数据库备份的数据文件在辅助副本的还原路径

SET @RestoreToLogFileFolder='D:\SQLLog'       --数据库备份的日志文件在辅助副本的还原路径

SET NOCOUNTON

PRINT '--============================================='

PRINT '--AlwayOn辅助副本还原数据库(指定NORECOVERY方式还原)'+CHAR(13)

DECLARE @RestoreFilePathNVARCHAR(MAX)

DECLARE @LNAMENVARCHAR(500)

DECLARE @PNAMENVARCHAR(500)

DECLARE @PFNameNVARCHAR(500)

DECLARE @BackupTypeCHAR(1)

SET @RestoreFilePath=''

SET @SQL= 'RESTORE FILELISTONLYFROM DISK = '''+@RestoreFromPath+'\'+@DBName+'.bak'+'''' 

if OBJECT_ID ('tempdb..#temp')is not null

BEGIN

 DROPTABLE #BackupFileList

END

CREATE TABLE#BackupFileList

    (

      LogicalNameNVARCHAR(128) ,

      PhysicalNameNVARCHAR(260) ,

      BackupTypeCHAR(1) ,

      FileGroupNameNVARCHAR(128) ,

      SIZENUMERIC(20,0),

      MaxSizeNUMERIC(20,0) ,

      FileIDBIGINT ,

      CreateLSNNUMERIC(25,0) ,

      DropLSNNUMERIC(25,0) NULL ,

      UniqueIDUNIQUEIDENTIFIER ,

      ReadOnlyLSNNUMERIC(25,0) NULL ,

      ReadWriteLSNNUMERIC(25,0) NULL ,

      BackupSizeInBytesBIGINT ,

      SourceBlockSizeINT ,

      FileGroupIDINT ,

      LogGroupGUIDUNIQUEIDENTIFIER NULL,

      DifferentialBaseLSNNUMERIC(25,0) NULL ,

      DifferentialBaseGUIDUNIQUEIDENTIFIER ,

      IsReadOnlyBIT ,

      IsPresentBIT ,

      TDEThumbprintNVARCHAR(100)

    )

INSERT INTO#BackupFileList EXEC (@SQL);

DECLARE CurTBNameCURSOR

FOR

    SELECTLogicalName,PhysicalName,BackupType FROM #BackupFileList 

OPEN CurTBName

FETCH NEXTFROM 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

    FETCHNEXT 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'' WITHNORECOVERY

GO'+CHAR(13)

PRINT @SQL

DROP TABLE#BackupFileList

以上是“如何实现alwayson的备份还原脚本”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注博信行业资讯频道！