Initialize SQL Server replication using a database backup

By:   |   Comments (38)   |   Related: 1 | 2 | 3 | 4 | 5 | > Replication


Problem

You need to configure transactional replication for a very large SQL Server database. Instead of running the snapshot agent to initialize the subscriber, in this tip we look at how you can initialize the subscriber from a SQL Server backup.

Solution

We need to be cautious when running the snapshot agent job in SQL Server replication when databases are very large, because of the overhead of exporting, transferring and importing the data into the subscriber. An alternative approach to initialize the subscriber database is to use a SQL Server database backup.

Snapshot Agent Settings

To do this, when setting up replication we need to make sure that we do not create the snapshot immediately while configuring the new publication. The below screen shot shows these items in the replication publication wizard in SQL Server Management Studio and shows that both options should be left unchecked.

using a sql server database back up

Allow Initialization From Backup Files

After the publication is created right click on your publication and select Properties. (In SSMS go to Replication > Local Publications > your publication and right click). On the properties window go to Subscription Options as shown below and change the "Allow initialization from backup files" setting to True.

in ssms go to replication>local publications

Backup Database on Publisher

After the above settings are made, we need to create a backup of the published database. In our example, the published database is R_PUB and the subscriber database is R_SUB.

Here is the backup command or this can be done via SSMS.

/*Take backup of publisher database*/
backup database R_PUB to disk ='C:\R_PUB.bak' with init, stats

Restore Database on Subscriber

Once the backup is done, we need to transfer the backup to the subscriber server and restore the database. Again this can be done using T-SQL as shown below or SSMS.

/*Restore the backup of publisher on subscriber*/
use master
go
restore database R_SUB from disk ='C:\R_PUB.bak' 
with move 'R_PUB' to 'C:\MSSQL\DATA\R_SUB.mdf',
move 'R_PUB_log' to 'C:\MSSQL\DATA\R_SUB_log.ldf', 
replace, 
stats

Create Subscription Using T-SQL

After the backup has been restored on the subscriber, on the publisher we need to add a subscription to this publication.

Note the last three parameters in the command below this is what tells SQL Server how to initialize the subscriber:

  • @sync_type - using a backup
  • @backupdevicetype - the backup was created on disk
  • @backupdevicename - the name of the backup file that was created
/*At the publisher, run the following command */
USE [R_PUB]
GO
EXEC sp_addsubscription 
@publication = R_PUB, 
@subscriber = 'Enter your subscriber server name here', 
@destination_db = R_SUB,
@sync_type = 'initialize with backup',
@backupdevicetype ='disk',
@backupdevicename = 'C:\R_PUB.bak'

Once you run above command, you will see a message similar to the below message.

create subscription using t-sql

Validating Snapshot Was Not Created

With this approach transactional replication is setup without running the snapshot agent job and you will also notice that the default snapshot folder remains empty since a snapshot was not generated.

validating snapshot was not created

Next Steps

Before implementing these steps in a production environment, it is important that you schedule it during a proper change window so that you would be able to configure this setup without any issues. We need to consider this point as there would be other replication maintenance jobs that would be running in the background like the "distribution clean up" job which will periodically clear the distribution database of transactions to replicate.

  • Consider configuring the initial snapshot for replication through backup especially for very large databases if you feel there might be performance issues
  • Analyze the space availability on both the publisher and subscriber servers as you need space for the full backups
  • Plan a suitable change window for performing these steps on a production environment
  • Be aware of the space impact of running the snapshot agent job
  • Refer to other related tips on replication to get familiar with the concepts


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, November 8, 2023 - 11:04:57 AM - Greg Robidoux Back To Top (91740)
Hi Balakrishna,

Yes if you do a backup and restore all data and database objects will exist in the restored database at the time the backup was taken.

What error or issues are you seeing with replication not working?

Wednesday, November 8, 2023 - 10:44:34 AM - Balakrishna Back To Top (91738)
i don't want to replicate all tables from publisher to subscriber, In our case we are taking backup on source and restoring it on destination so all tables will get replicated right ?

And when i done same steps which you have given the data is not syncing and no errors. Could you please help me on this.

Wednesday, June 28, 2023 - 10:48:37 AM - Edgar Walther Back To Top (91350)
HI, thanks for your great article... I'm almost there.
Only issue left is that it doesn't replicate the changes...
Is there something I need to do at the subscriber as well?
Or something else?

Monday, August 30, 2021 - 7:01:54 PM - Gilberto Back To Top (89194)
I have quite a few issues in a peer-to-peer configuration. I removed one of the peers, then added it back using a fresh backup of the published database. Upon reinstating, replication was successful from N1 to N2 and N3, from N2 to N1 and N3. However, Changes in N3 were not being replicated to N1 and N2; message received was "Could not find stored procedure 'sp_MSins_dbodata_types10186418590213799075'"
What is the correct procedure for removing a Node and then adding it back (or otherwise adding a new Node) to the existing peer-to-peer environment? Much thanks.

Thursday, January 9, 2020 - 5:25:52 PM - Mohammed Back To Top (83664)

Ahmed,

My suggestion is you do some reading on replication. There are some good options availbale. One of them is here.

https://www.sqlservercentral.com/stairways/stairway-to-sql-server-replication

Thanks,

Thursday, January 9, 2020 - 1:21:54 AM - Ahmed Raza Back To Top (83656)

Mohammed

Thank you for your valuable article. I have just started in a DBA role and have many challenges. I would like to use replication in a production and development environment but not sure what type of replication will be suitable for this, could you kindly suggest what is best way to replicate with little or no maintenance required.

Monday, November 18, 2019 - 8:47:04 AM - David Curtin Back To Top (83123)

If you onyl replciate 10 tables out of 100's will it remove the others restored in the backup?

Friday, August 16, 2019 - 7:21:17 PM - M Moinudheen Back To Top (82087)

Yes, you could disable the replication jobs but make sure to re-enable them again.

Friday, August 16, 2019 - 11:35:33 AM - eric81 Back To Top (82082)

Would it be a good idea to disable all of the replication maintenacne jobs while you are initializing from backup file?

Tuesday, June 4, 2019 - 12:42:27 PM - Carla Romere Back To Top (81325)

I have used these instructions before with great success. However, after having some replications errors in our test environment, I decided to drop replication and start it up again. I have an approximately 1tb database that I'm replicating. I followed these instructions and everything appears to be up and running, but nothing is replicating. I don't see any obvious errors. I'm rather stumped at this point as to how to get the database replicating once again.

Thanks,
Carla

Friday, June 29, 2018 - 8:28:58 PM - Ashu Back To Top (76468)

Hi Mohammad,

How to backup only certain articles from publisher database and restore it to subscriber?

Thursday, January 26, 2017 - 5:33:07 PM - CJ Morgan Back To Top (45603)

 So I believe you DO NOT have to keep the subscriber up to date with log backups until you create the subscription.  At least for SQL 2014 this is stated in the following article:
https://msdn.microsoft.com/en-us/library/ms151705(v=sql.120).aspx

When restoring a backup, you must ensure that the backup came from the Publisher if you want the Subscriber to automatically synchronize. The log sequence number (LSN) values in the backup (which are used to set the point at which to start synchronizing) are specific to the Publisher.

As long s the backup is not older than your distribution cleanup setting (72 hours by default) you should be good if the backup is less than say 24 hours old.  Distributor should apply the changes after the backup per the article above).  That is how I read that and it's a game changer for me.

Thursday, January 26, 2017 - 4:58:43 PM - CJ Morgan Back To Top (45602)

So do I understand your reply to Jeff that the transactions between the time you take the backup/copy to subscriber/restore (and for me this process is over 6-8 hours alone) the transactions done during that windows are NOT captured by the distributor and then applied to subscriber after the subscription is setup?

If that is the case I can deal with that, just have to plan this around the customer's maintenance window and do full backup/diff/log restores until we cut traffic off.  I'd love to have some clarification on this.

 

Friday, January 22, 2016 - 9:13:05 AM - eric81 Back To Top (40478)

 

Can you re-initialize off of a compressed SQL backup? I would suspect there answer is yest but I haven't read that anywhere.

Friday, October 2, 2015 - 4:25:57 PM - Pramod Kumar P C Back To Top (38816)

 

Dear Mr. M.M,

Thanks for your valuable post. I would like to ask you that the user can save time by using 'Alternate folder' option in Subscription to locally initialize so I feel it is a better alternate to Initialize quickly. What is your comment about it ?

 

Friday, February 13, 2015 - 2:55:14 AM - Rahdix Back To Top (36224)

Hi Mohammed, I just read Your article but I still confuse with this method. So we must backup the database from the publisher everyday, put it in the path which has been set by using t-sql script before (like shown in the article) and let the agent do the job?please correct me if I miss understand.

Friday, January 3, 2014 - 2:35:55 PM - Luis Sanabria Back To Top (27953)

Mohammed, thanks for this great article.

Right now I have a replica server that was created using the snapshot method. When I need to add or remove an article(table, SP, views or functions) I go to the publication properties and on the "Articles" page I check or unckeck the one I would like to add or remove from the replication. 

One question, if I use this database method would I be able to manage my replicated articles using this same method?

Regards, Luis

Thursday, October 3, 2013 - 6:00:08 PM - Mohammed Back To Top (27030)

WA Omran,

Thank you sir for the kind words.

Regards,

Mohammed

Thursday, October 3, 2013 - 5:38:43 PM - Omran Abdurrahman Back To Top (27029)

AA Mohammed, I used your article for creating replication using backups many times, everytime it worked well.

Thank You Very much Sir!

Omran

Monday, June 10, 2013 - 6:02:31 AM - Naresh Back To Top (25367)

Hi Mohammed, I used the above method for creating replication using backups, but I am getting following errors, any ideas?

Msg 20027, Level 11, State 1, Procedure sp_MSadd_subscription, Line 223

The article '(null)' does not exist.

Msg 14070, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1208

Could not update the distribution database subscription table. The subscription status could not be changed.

Msg 14057, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 383

The subscription could not be created.

 

Tuesday, March 12, 2013 - 6:13:15 PM - Jeff Back To Top (22762)

Thank you Mohammed for the quick reply.  I was hoping there would be a replication command or feature that I was missed.  I understand we could continually apply transaction log backups; however, this might not be enough on a 24x7 database that receives insert/updates/deletes all the time.  I did not want to reinitialize a whole new snapshot if I can prevent it.  I thought the distributor would store all (missing) changes and apply them once the backup has been restored and the subscriber is online.

-Jeff.

Tuesday, March 12, 2013 - 5:02:14 PM - Mohammed Back To Top (22761)

Hi Jeff,

We could use the transaction log backups itself for this. Please increase the frequency of the transaction log backup and you would be able to bring it in sync with the production publisher database.

Thanks,

Tuesday, March 12, 2013 - 3:41:49 PM - Jeff Back To Top (22759)

Mohammed,

For an active production database that we want to replicate with an initial backup, how can we replicate data that is inserted/updated/deleted from the timespan of when the backup has completed to when it get's restored and setup as a subscriber?  It could take us about an hour or two before we get the backup restored and subscriber setup.  We could apply transaction logs afterwards, but there still could be a window of missed data.  How can we get the missing data to the subscriber?

thanks,

Jeff.

 

Wednesday, January 16, 2013 - 6:44:47 AM - baguazhang Back To Top (21477)

Is there a way to initialise in this way whilst providing an Agent Processing Account? When i use this method the initial startup fails due to the job running under an unauthorised account. if I then change the account in the subscription properties it seems to run ok in the replication monitor however it does not actually ship any data to the subscriber.

 

Many thanks

 

Saturday, December 8, 2012 - 2:18:07 AM - Mohammed Moinudheen Back To Top (20820)

Dawn,

I am not sure on this as I used only native backups for testing.

Thanks

 

Friday, December 7, 2012 - 10:35:28 AM - Dawn Back To Top (20807)

Does the backup have to be a native SQL backup or can I use a third party tool (Idera SQL Safe) to compress the backup file? My replicated DB is SQL 2005 replicated to SQL 2008.

 

thanks

Tuesday, October 9, 2012 - 7:45:31 PM - Sandeep Back To Top (19841)

In my sql server "Allow initialization from backup files" is missing in my version of SQL Server 2008 Enterprise version.

Thursday, July 19, 2012 - 2:04:01 PM - Mohammed Moinudheen Back To Top (18663)

Jim,

Thank you for the feedback, very good point.

Regards

 

Thursday, July 19, 2012 - 11:35:50 AM - Jim Chatleain Back To Top (18656)

Just want to say that your instructions are fantastic with one exception I was not able to use your script to restore (SQL2005) would not let me so I used standard Restore so I could force the DB overwrite. as long as your backup is stored locally on the Subscriber server no issues. Also must tell users that absolutly nothing can be running on the replication server, that includes the application using this SQL DB.

 

 

Thursday, May 31, 2012 - 9:57:53 AM - Mohammed Moinudheen Back To Top (17748)

Sidd,

Thanks for the positive comments. If you notice, after manually adding the new article in publication the table still does not appear in the subscription database. And when you insert some values in the table in publication db, replication fails with this error message "Invalid object name 'dbo.your_new_table'. (Source: MSSQLServer, Error number: 208). "

In order to get replication running either way, try scripting out the table that you manually added in publication and create it on the subscription. Wait for few minutes and the data should get replicated from the publisher to subscriber.

Thanks.

Mohammed Moinudheen

 

 

 

Thursday, May 31, 2012 - 7:17:59 AM - Sidd Back To Top (17744)

Mohammed,

 Thanks for the excellent article. 

we created a replication out of a DB back up and its working real well. my question is how do we handle if we add new tables in the primary DB. i know that the new tables does not automatically show up in the selected artilces under the publication properties and needs to be manually added. however after doing this i am still not able to replicate the data of this table over to the subscriber DB. 

 

any advices on what i am missing here?

 

-Sidd

Monday, September 19, 2011 - 5:41:40 PM - Dan Back To Top (14691)

Thank you for taking the time!  I did not do the transaction log option, I will use that .  Thank you!

Dan

Sunday, September 18, 2011 - 1:37:31 AM - Mohammed Moinudheen Back To Top (14686)

Hi Dan,

Thank you for your kind words.

I tested your scenario on my lab server and it worked fine. I was able to verify that the replication was still happenning between the publisher, the initial subsriber server and the newly added subsriber (through backup method). However, in my lab scenario, the subscription type was push for both the subsribers. You might need to test further for your specific scenario. Please note, I tested this on SQL Server 2008 R2.

Regarding, guaranteeing that the records created\modified since publisher DB backup is reflected on the subscriber, you might need to consider taking transaction log backups following the full backup taken on the publisher and restoring them with the 'Recovery option'.

Hope this helps.

Regards,

Mohammed Moinudheen

 

Friday, September 16, 2011 - 2:40:20 PM - Dan Back To Top (14680)

Hi, Mohammed:

Thank you for this excellent article. 

Here is my situation:

I already have a publication set up with snapshoot ... immediately ... under "Snapshoot Agent Settings"  checked.  There is already a pull-subscription to another serevr in production for this publication  and  I can't stop it.

What I want to do is create a new Subscription to the same publication on another server in production, but since the DB is over 100 GB, I want to use a backup as a snapshoot.  How do I go about doing that?

Also, once the snapshoot is created from backup, how can I gurantee that the records created/modified (transcations) since the DB backup were taken will be updated on the subscriber?

Thank you in advance.

Dan

Thursday, September 8, 2011 - 7:54:28 AM - Paul Henry Back To Top (14612)

It won't work in 2000, the option in properties to 'Initialize using a database backup' does not exist. It first appeared in 2005.

Wednesday, August 31, 2011 - 1:40:42 AM - Mohammed Moinudheen Back To Top (14574)

Bill, I agree with Greg, I didn't get the chance to try this on SQL Server 2000. I tested this from SQL Server 2005 onwards.

Tuesday, August 30, 2011 - 2:57:02 PM - Greg Robidoux Back To Top (14568)

Bill,

this works for versions SQL 2005, 2008 and 2008 R2.  I am not sure about SQL 2000.

 

Tuesday, August 30, 2011 - 1:48:41 PM - Bill Back To Top (14564)

Good article.  What versions is this valid for?  2000?  2005?  2008?  2008R2?














get free sql tips
agree to terms