使用複制進行 SQL Server 數據庫還原

如果這聽起來很生氣/咆哮，請原諒我，但確實如此。

高級概述：

如何還原涉及具有 5 個發布/訂閱的事務複製的 SQL 數據庫，而無需刪除、創建和重新初始化（或重新快照）所有訂閱。或者至少，無需重新初始化/重新快照。我可以自己處理髮布/訂閱的腳本，這是可以理解的（有點）。但是，我有一個已知的良好備份，和/或願意在主伺服器上進行“新”備份以與輔助伺服器共享，以嘗試在創建訂閱時使用“從備份初始化”功能，和我似乎無法讓它發揮作用。

咆哮的版本：

為什麼 SQL Server 不能“相信我”發布者和訂閱者 DB本質上是同步的，因為它們是從相同的備份文件中恢復的，並且複制可以簡單地繼續以愉快的方式進行，而不必執行該死的重新初始化或重新-從頭開始創建！？

---

細節：

我有一個數據庫MyCoolDB。它生活（起源）在 server 上OldFoo。在“舊”環境中，它被複製到 server OldBar，用於只讀報告目的。我正在準備遷移到具有類似名稱的伺服器NewFoo和NewBar. 出於本討論的目的，Foo伺服器將是發布者，Bar伺服器將是訂閱者。為簡化起見，我們將讓發布者兼任分發者，OldFoo即將發布和分發到OldBar，對於NewFooto 也是如此NewBar。

在“新”環境中，我想使用相同的備份文件MyCoolDB恢復到NewFoo(pub) 和NewBar(sub) 。 並且，因為我在最終切換之前多次執行此操作以刷新所述環境，所以我不想每次都刪除、重新創建、重新初始化或重新快照 pubs/subs 。

這是不是要求太過分了？當涉及到可能參與複製的 SQL DB 時，肯定有一種方法可以從更高的（Prod/Pre-Prod）環境“刷新”開發和 QA 環境。

假設/註釋：

1. 我可以通過停止 Repl 類別（分發和日誌讀取器代理）中的關聯代理作業來有效地“暫停”複製。 這可能是一個糟糕的假設。
2. 我可以快速有效地MyCoolDB從磁碟上的本機備份文件恢復，並且兩個伺服器實例都可以訪問。
3. NewFoo我已經通過從生成的腳本OldFoo和執行字元串替換來編寫實例的 repl pub/sub 創建。我還驗證了，當使用所述創建腳本並以正常方式初始化時，事情會按預期工作。
4. 我已經查看了聲稱通過“從備份初始化訂閱”過程但沒有找到有效解決方案的 文章和部落格文章。我靠近了；詳情如下。
5. 雖然這裡所討論的環境絕對不是生產環境，但這是涉及類似問題（複製數據庫、重新初始化導致性能問題）的生產遷移的所有準備和實踐。

---

關閉，但沒有雪茄：

在嘗試了這種方法之後…

• 刪除發布/訂閱
• 在 Publisher 上恢復數據庫
• 創建 Pub
• 將數據庫備份到特殊位置
• 從特殊位置恢復訂閱伺服器上的數據庫
• 在特殊位置使用 init-from-backup 創建 Subs

我檢查了訂閱的 Repl-Monitor，在 Log Reader Agent 狀態下，顯示以下錯誤。下面的錯誤是我嘗試這樣做的原因exec sp_replrestart，但正如我所說，那也失敗了。

The process could not execute 'sp_repldone/sp_replcounters' on 'NewFoo'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The log scan number (267414:8865:55) passed to log scan in database 'Grading' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Source: MSSQLServer, Error number: 9003)
Get help: http://help/9003
The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
The process could not execute 'sp_repldone/sp_replcounters' on 'NewFoo'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

有接盤俠嗎？

TL;DR：我的主要目標是了解如何從備份中初始化複製訂閱，這樣就不需要重新初始化/重新快照。我的次要目標是避免每次都必須刪除和重新創建（從腳本）發布/訂閱，但在這一點上，這是“很高興擁有”，而不是“要求”。

實際上，@NateJ，你快到了。但不是在特殊位置使用 init-from-backup 創建 Subs，您需要使用 @sync_type=‘replication support only’ 添加訂閱，如下所示

EXEC sp_addsubscription @publication = N'Test',  
                       @subscriber = N'subscriber',  
                       @destination_db = N'SubDB',  
                       @subscription_type = N'Push',  
                       @sync_type = N'replication support only',  
                       @article = N'all'

一種簡單的方法是通過查找 sp_addsubscription 並更改 @sync_type 值來修改以前編寫的複制腳本。

我以前經常這樣做。（我假設您的複制是帶有事務發布的推送訂閱）

首先讓我說不建議將複製數據庫從 domain1 恢復到 domain2。您將需要編寫所有複製腳本並重新應用腳本，包括新域中代理的帳戶和密碼。

如果它在同一個域上，那麼所有代理帳戶都已經擁有伺服器上的所有必要權限 - 發布者、分發者和訂閱者，那麼我一直在恢復複製的數據庫，而無需先編寫腳本，然後再應用腳本。

這是我所做的：

1. 備份

2）腳本所有使用者和權限

3. 為所有複製編寫刪除和創建腳本 ( just in case you need to come to this)

4. 恢復、故障排除和測試

---------------------------------------

-- 5.1 --> if the database is involved in replication 
      -- check if it is Sync With backup

--At the Distributor, use the DATABASEPROPERTYEX (Transact-SQL) function to return the IsSyncWithBackup property of the distribution database. 


--IsSyncWithBackup
--The database is either a published database or a distribution database, 
--and can be restored without disrupting transactional replication.

--1 = TRUE
--0 = FALSE
--NULL = Input not valid

-- if that is false, then enable it
-- How to: Enable Coordinated Backups for Transactional Replication (Replication Transact-SQL Programming)
-- http://msdn.microsoft.com/en-us/library/ms147311(v=sql.105).aspx

-- get the distributor
EXEC sp_get_distributor
GO
-- get the distributor server and distributor database too
EXEC sp_helpdistributor
GO
--SQLDISTLON1
-- distribution

use product
go
-- at the product database
declare @db sysname
select @db = 'Product'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--0

--change the value to TRUE
sp_replicationdboption @dbname= 'Product',
                      @optname= 'sync with backup',
                      @value='true'

---------------------------------------

--Script to Enable/Disable Database for Replication
--I DID NOT DO THIS
--use master
--exec sp_replicationdboption @dbname = 'Product',
--@optname = 'publish',
--@value = 'false'
--go


--=====================================================================================
-- THE RESTORE
-- note the backup of the tail of the transaction log alongside the restore.
-- note also the KEEP_REPLICATION option
--=====================================================================================


USE [master]
ALTER DATABASE [Product] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [Product] TO  DISK = N'F:\SQLBackups\UserDB\Product\SQLAPPLON1_the_tail_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'tail log backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 1
RESTORE DATABASE [Product] FROM  DISK = N'F:\SQLBackups\UserDB\Product\SQLAPPLON1_Product_FULL_20140909_222917.bak' 
WITH  FILE = 1,  MOVE N'Product' TO N'E:\SQLData\Product.mdf', 
MOVE N'Product_log' TO N'E:\SQLLogs\Product_log.ldf',  
KEEP_REPLICATION,  
NOUNLOAD,  
REPLACE,  STATS = 1
go
ALTER DATABASE [Product] SET MULTI_USER

GO
--=====================================================================================
-- THE RESTORE - FINISH
--=====================================================================================



---------------------------------------
-- set sync with backup to false (unless decided otherwise)
-- problem with this is that it slows the log reader a bit

use product
go
-- at the product database
declare @db sysname
select @db = 'Product'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--0

--change the value to TRUE
sp_replicationdboption @dbname= 'Product',
                      @optname= 'sync with backup',
                      @value='FALSE'

-- at the product database
declare @db sysname
select @db = 'Product'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--0

---------------------------------------
--Script to Enable/Disable Database for Replication
use master
exec sp_replicationdboption @dbname = 'Product',
@optname = 'publish',
@value = 'True'
go



---------------------------------------
-- There were some problems
-- the backup that I needed to use to restore the DB was much older and from a different server


-- I used these two commands
-- from inside the Product database (in the publisher)

use Product
go

DBCC OPENTRAN 
sp_replrestart

-- http://msdn.microsoft.com/en-us/library/ms174390.aspx

--sp_replrestart is an internal replication stored procedure and s
--hould only be used when restoring a database published in a transactional replication 
--topology as directed in the topic Strategies for 
--Backing Up and Restoring Snapshot and Transactional Replication.

--Used by transactional replication during backup and restore so that 
--the replicated data at the Distributor is synchronized with data at the Publisher. 
--This stored procedure is executed at the Publisher on the publication database.

--How if works? 
--sp_replrestart will fill NO-OP (No-Operation) transaction which will cause the 
--LSN's to increase until the log LSN matches the LSN as per Distribution database. 
--So from there-on, Logreader agent will be able to read the LSN, it is expecting.

--Note: Depending on how old the backup of the Published database which was restored, 
--it may take hours for this operation and may make the transaction log to grow big.
--and grow by gigs, until they match.



--=====================================================================================
-- THE TEST
-- I add the folowing table to the published database
-- then I add the article to the publication
-- start a snapshot
-- and check if the article is in the subscription
--=====================================================================================

use product
go
--DROP TABLE dbo.marcelo_test

create table dbo.marcelo_test(
i int not null identity(1,1) not for replication
,the_name varchar(40) not null )

insert into marcelo_test values ('belluno')
insert into marcelo_test values ('rovigo')
insert into marcelo_test values ('feltre')
insert into marcelo_test values ('cremona')
insert into marcelo_test values ('padova')
insert into marcelo_test values ('vicenza')
insert into marcelo_test values ('venezia')


select * from dbo.marcelo_test

alter table dbo.marcelo_test
add constraint pk_marcelo primary key clustered (i)

現在，這不是一個完整的解決方案，更多的是我在恢復複製數據庫時的筆記，我所做的故障排除，一些解決可能問題的方法，以及最後的一個小測試，在我讓使用者知道數據庫處於活動狀態之前.

希望能幫助到你

引用自：https://dba.stackexchange.com/questions/150218