{"id":7014,"date":"2022-10-19T10:56:00","date_gmt":"2022-10-19T10:56:00","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/initializing-replication-for-a-large-database\/"},"modified":"2026-11-03T19:27:38","modified_gmt":"2026-11-03T19:27:38","slug":"initializing-replication-for-a-large-database","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/de\/blogs\/initializing-replication-for-a-large-database\/","title":{"rendered":"Initializing replication for a large database"},"content":{"rendered":"<p>This blog demonstrates the detailed steps needed to set up transactional replication using a backup to initialize the subscriptions without taking the snapshot for all the articles, as we know the snapshot option is time-consuming for large databases.&nbsp;<br \/>\n&nbsp;<\/p>\n<p>Introduction<br \/>\nSetting up a transactional replication for an extensive SQL Server database is a huge task. Generally, you will set up transactional replication on any database by generating the snapshot without considering how long a snapshot will take and the amount of disk for the snapshot folder. To avoid a very long time to create an initial snapshot and reinitialize the Subscribers, in this blog, you will explore an easy way to initialize the subscriber from an SQL database backup.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Let\u2019s get into the detailed steps of the setup:<\/strong><\/p>\n<p>\nTo set up a transactional replication, you must first configure the Distributor.&nbsp;<br \/>\nIn this lab, I have already configured the Distributor, and the following is the reference <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/replication\/configure-publishing-and-distribution?view=sql-server-ver15\">link<\/a> on how to configure the Distributor.<\/p>\n<p><strong>Process Steps Overview<\/strong><\/p>\n<ul>\n<li>Create Publication.&nbsp;<\/li>\n<li>Modify Publication Properties. &nbsp;<\/li>\n<li>Back up the Publisher database.<\/li>\n<li>Restore the backup on the subscriber database. &nbsp;<\/li>\n<li>Create a Subscription through T-SQL as the GUI doesn\u2019t support initializing from a database backup.<\/li>\n<\/ul>\n<p><strong>In this scenario, Transactional Replication is going to be configured with the following details:<\/strong><\/p>\n<ul>\n<li>Publisher + Distributor: Node1<\/li>\n<li>Subscriber: Node2<\/li>\n<li>Publisher Database: ABC_Pub<\/li>\n<li>Subscriber Database: ABC_Sub<\/li>\n<li>Publication: ABC_Pub_Bkp<\/li>\n<li>Subscription: ABC_Sub_Bkp<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>Step 1: Create the publication by choosing the database for replication.&nbsp;<br \/>\nPublisher Database: ABC_Pub<\/strong><\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture1_4.png\" alt=\"DB Replication\" \/>\n<\/p><\/blockquote>\n<p><strong>Step 2: Select the type of publication and the tables to participate in replication.&nbsp;<\/strong><\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture2_5.png\" alt=\"DB Replication Picture 2\" \/>\n<\/p><\/blockquote>\n<p>&nbsp;<br \/>\n<strong>Select Articles to replicate:&nbsp;<\/strong><\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture3_5.png\" alt=\"DB Replication Picture 3\" \/>\n<\/p><\/blockquote>\n<p><strong>Step 3: In our case, we are using a database backup file instead of a snapshot so we will leave them blank and click on next, as shown in the following snapshot.&nbsp;<\/strong><\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture4_7.png\" alt=\"DB Replication Picture 4\" \/>\n<\/p><\/blockquote>\n<p><strong>&nbsp;Created publication- ABC_Pub_Bkp&nbsp;<\/strong><\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture5_3.png\" alt=\"DB replication Picture 5\" \/>\n<\/p><\/blockquote>\n<p><strong>Step 4: Set \u2018Allow initialization from backup files\u2019 to true.&nbsp;<\/strong><\/p>\n<blockquote>\n<p>From T-SQL :<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture6.PNG\" alt=\"DB replication Picture 7\" \/>\n<\/p><\/blockquote>\n<p><strong>From GUI :&nbsp;<\/strong><br \/>\nOn Publication Properties, select the Subscription Options and set \u201cAllow initialization from backup files\u201c to \u201ctrue\u201d and Click Ok to save the change.<\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture7_3.png\" alt=\"DB replication Picture 7\" \/>\n<\/p><\/blockquote>\n<p><strong>&nbsp;&nbsp;Disable the Distribution cleanup SQL Server job (right-click on the job name and click Disable)<\/strong>:<\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture8_3.png\" alt=\"DB replication part 8\" \/><\/p>\n<p><em>It is a must to disable the job before taking the backup as a next step. If not, you may experience the following error&nbsp;<\/em><\/p>\n<\/blockquote>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture9_0.png\" alt=\"DB replication Picture 9\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture10_3.png\" alt=\"DB replication Picture 10\" \/><\/p>\n<p>Execute sp_addsubscription on the Publication database by passing the mentioned parameters in the following snapshot.&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture11_0.png\" alt=\"DB Replication Picture 11\" \/><\/p>\n<p>After all the other steps are completed, enable the Distribution cleanup job.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture12_2.png\" alt=\"DB replication Picture 12\" \/><\/p>\n<p>Now check the replication status using SSMS under replication \u2192 Replication Monitor.<\/p>\n<p>&nbsp;<\/p>\n<p>We can see that the performance shows an excellent state and there is no Snapshot agent created in the entire process as we have used the backup file to initialize.&nbsp;&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture12_3.png\" alt=\"DB replication Picture 12\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26065-body-Picture13_0.png\" alt=\"DB replication Picture 13\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Initializing replication for a large database<\/p>\n","protected":false},"author":2,"featured_media":7016,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26065","content_type":"blog","created_by":"10130","authored_on":"2023-09-20T10:56:00+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Initialize transactional replication for large SQL Server databases efficiently. Increase system efficiency without time-consuming snapshots.","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26065-insights_image-database-technical-blog-2-insights.jpg","field_partners":"","rxt_platforms":"Data","product":"","publisheddate":"2022-10-19T10:56:00","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Initializing Replication for a Large Database","field_topics":"","field_slug":"\/initializing-replication-for-a-large-database","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7014","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7014","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=7014"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7014\/revisions"}],"predecessor-version":[{"id":8742,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7014\/revisions\/8742"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/7016"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=7014"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=7014"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=7014"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}