使用T-SQL Prepare a secondary database for an Always On availability group

2022-07-10 21:49 abce 阅读(22) 评论(0) 编辑收藏举报

--在主副本节点上修改数据库属性
--在SQLSERVER 2012中，默认是simple恢复模式
use master;
go
alter database abce set recovery full;
go
 
--在主副本节点上全库备份
DECLARE @CurrentTime VARCHAR(50), @FileName VARCHAR(200)
SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')
  
--(abce 数据库完整备份)
SET @FileName = 'D:\abce_FullBackup_' + @CurrentTime+'.bak'
BACKUP DATABASE [abce]
TO DISK=@FileName WITH FORMAT ,COMPRESSION
  
--(abce 数据库日志备份)
SET @FileName = 'D:\abce_logBackup_' + @CurrentTime+'.bak'
BACKUP log [abce]
TO DISK=@FileName WITH FORMAT ,COMPRESSION
  
--在辅助副本节点上进行还原数据库
USE [master]
RESTORE DATABASE [abce] FROM  DISK = N'd:\abce_FullBackup_2022_07_10_172018.bak' WITH  FILE = 1,
MOVE N'abce' TO N'D:\DATA\abce.mdf',
MOVE N'abce_log' TO N'D:\DATA\abce_log.ldf',
NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5
  
GO
-- 还原日志 一定要用NORECOVERY
USE [master]
RESTORE DATABASE [abce] FROM  DISK = N'd:\abce_logBackup_2022_07_10_172018.bak' WITH  FILE = 1,
NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5
  
GO
 
--完成还原后，将数据库加入AG
--在辅助副本节点上执行
ALTER DATABASE abce SET HADR AVAILABILITY GROUP = MyAG;

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/join-a-secondary-database-to-an-availability-group-sql-server?view=sql-server-ver16
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver16