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