如何实现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的备份还原脚本”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注编程网数据库频道！