共计 2903 个字符，预计需要花费 8 分钟才能阅读完成。
这篇文章主要介绍如何实现 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 的备份还原脚本”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！
