Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Initialize SQL Server replication using a database backup

MSSQLTips author Mohammed Moinudheen By:   |   Read Comments (22)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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


Last Update: 8/30/2011


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.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, August 30, 2011 - 1:48:41 PM - Bill Read The Tip

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


Tuesday, August 30, 2011 - 2:57:02 PM - Greg Robidoux Read The Tip

Bill,

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

 


Wednesday, August 31, 2011 - 1:40:42 AM - Mohammed Moinudheen Read The Tip

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.


Thursday, September 08, 2011 - 7:54:28 AM - Paul Henry Read The Tip

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


Friday, September 16, 2011 - 2:40:20 PM - Dan Read The Tip

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


Sunday, September 18, 2011 - 1:37:31 AM - Mohammed Moinudheen Read The Tip

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

 


Monday, September 19, 2011 - 5:41:40 PM - Dan Read The Tip

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

Dan


Thursday, May 31, 2012 - 7:17:59 AM - Sidd Read The Tip

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


Thursday, May 31, 2012 - 9:57:53 AM - Mohammed Moinudheen Read The Tip

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, July 19, 2012 - 11:35:50 AM - Jim Chatleain Read The Tip

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, July 19, 2012 - 2:04:01 PM - Mohammed Moinudheen Read The Tip

Jim,

Thank you for the feedback, very good point.

Regards

 


Tuesday, October 09, 2012 - 7:45:31 PM - Sandeep Read The Tip

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


Friday, December 07, 2012 - 10:35:28 AM - Dawn Read The Tip

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


Saturday, December 08, 2012 - 2:18:07 AM - Mohammed Moinudheen Read The Tip

Dawn,

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

Thanks

 


Wednesday, January 16, 2013 - 6:44:47 AM - baguazhang Read The Tip

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

 


Tuesday, March 12, 2013 - 3:41:49 PM - Jeff Read The Tip

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.

 


Tuesday, March 12, 2013 - 5:02:14 PM - Mohammed Read The Tip

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 - 6:13:15 PM - Jeff Read The Tip

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.


Monday, June 10, 2013 - 6:02:31 AM - Naresh Read The Tip

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.

 


Thursday, October 03, 2013 - 5:38:43 PM - Omran Abdurrahman Read The Tip

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

Thank You Very much Sir!

Omran


Thursday, October 03, 2013 - 6:00:08 PM - Mohammed Read The Tip

WA Omran,

Thank you sir for the kind words.

Regards,

Mohammed


Friday, January 03, 2014 - 2:35:55 PM - Luis Sanabria Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.