see below how I successfully restore replication (transactional replication).
1 - I script everything related to that publication - so that I can create it again from those scripts if things go wrong 2 - I script all the user and permissions in the database I want to restore (if the backup comes from a different environment/server) 3 - disable any processes that hit the database you want to restore 4 - backup the current database 5 - restore verifyonly the backup you have just created (in case you need to restore it) 6 - see if you have enough disk space 7 - check if the database is involved in replication --------------------------------------- --> 5 - see if the database is involved in replication (transactional replication) --------------------------------------- SELECT name as [Database name], CASE is_published WHEN 0 THEN 'No' ELSE 'Yes' END AS [Is Published], CASE is_merge_published WHEN 0 THEN 'No' ELSE 'Yes' END AS [Is Merge Published], CASE is_distributor WHEN 0 THEN 'No' ELSE 'Yes' END AS [Is Distributor], CASE is_subscribed WHEN 0 THEN 'No' ELSE 'Yes' END AS [Is Subscribed] FROM sys.databases WHERE database_id > 4 go use DB1 go -- at the DB1 database declare @db sysname select @db = 'DB1' SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' ) --0 --change the value to TRUE sp_replicationdboption @dbname= 'DB1', @optname= 'sync with backup', @value='true' --===================================================================================== -- 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 [DB1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE BACKUP LOG [DB1] TO DISK = N'F:\SQLBackups\UserDB\DB1\SQLAPPLON1_the_tail_log.trn' WITH NOFORMAT, NOINIT, NAME = N'tail log backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 1 RESTORE DATABASE [DB1] FROM DISK = N'F:\SQLBackups\UserDB\DB1\SQLAPPLON1_DB1_FULL_20140909_222917.bak' WITH FILE = 1, MOVE N'DB1' TO N'E:\SQLData\DB1.mdf', MOVE N'DB1_log' TO N'E:\SQLLogs\DB1_log.ldf', KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 1 go ALTER DATABASE [DB1] 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 DB1 go -- at the DB1 database declare @db sysname select @db = 'DB1' SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' ) --1 --change the value to False sp_replicationdboption @dbname= 'DB1', @optname= 'sync with backup', @value='FALSE' -- at the DB1 database declare @db sysname select @db = 'DB1' SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' ) --0 --------------------------------------- --Script to Enable/Disable Database for Replication use master exec sp_replicationdboption @dbname = 'DB1', @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 DB1 database (in the publisher) use DB1 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 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 -- when I generated the SNAPSHOT - it only generate this ONE ARTICLE --===================================================================================== use DB1 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)