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.
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.
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.
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*/
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',
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 */
@publication = R_PUB,
@subscriber = 'Enter your subscriber server name here',
@destination_db = R_SUB,
@sync_type = 'initialize with backup',
@backupdevicename = 'C:\R_PUB.bak'
Once you run above command, you will see a message similar to the below message.
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.
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
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 ?
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 03, 2014 - 2:35:55 PM - Luis Sanabria
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?
Thursday, October 03, 2013 - 6:00:08 PM - Mohammed
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.
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?
Wednesday, January 16, 2013 - 6:44:47 AM - baguazhang
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.
Saturday, December 08, 2012 - 2:18:07 AM - Mohammed Moinudheen
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
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.
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.
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'.
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.
Thursday, September 08, 2011 - 7:54:28 AM - Paul Henry