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