How to replicate SQL 2008 database to SQL 2014 by using Initialize from Backup.

 
How to replicate DBA database from SQL 2008 build 10.50.4000 to DBA database on SQL 2014 12.0.4100.1

NOTE/CAVEAT: This method does not allows to select and replicate only a subset of tables from the Publisher database but the entire table list.

1. On SQL 2008 - Add “Transactional Publication” Publication WITHOUT any initialization/snapshot ONLY if there is no publication for the same database. If there is one publication already we will use that one and just add another subscriber. You can use GUI or generate script and run it – just make sure you do NOT check boxes “Create Snapshot Immediately…” and “Schedule…” - I used SQL Server Agent under security when asked just to get it going but later I switched it to a SQL login as my servers were on separate non-trusted domains.
 
2. check publication and change initialize status for that particular publication

sp_helppublication
                      sp_changepublication  @publication= N'DBA2BI2' , @property = N'allow_initialize_from_backup', @value=N'true'
                      

Select all Open in new window

 
3. STOP or update the SQL job schedule for “Distribution clean up: distribution” Publisher job to 10/12 hours instead of 10 minutes to avoid missing transactions in the Distribution database between  the time the publication was created and the time the subscription can be created (consider long backup/copy/restore where 10 minutes is impossible to beat).
 
4. Backup published DBA database and copy to BI2SQL for restore – you can use COPY_ONLY FULL backups to not mess up your existing backups.
 
5. Restore at subscriber – (you can ZIP/compress it for copy to be faster if you don't have a share that can be used between the two).
 
7. Drop ALL triggers in the Subscription database restored from the Publisher by running because you don’t want Subscriber data messed up as triggers that fired already in publisher will fire again in Subscriber on any Insert/Update/Delete via replication.
 
8. On publisher run statement below and pay ATTENTION to the Publication Name:
 
***Make sure publication is set to “Allow initialization from backup files” to “True” as per Step 2 above.
 

USE DBA
                      GO
                      EXEC sp_addsubscription @publication= N'DBA2BI2',
                      						@sync_type = N'initialize with backup',
                      						@backupdevicetype = N'disk',
                      						@backupdevicename = N'M:\My_BKPs\DBA.bak',
                      						@subscriber = 'xx.xxx.xxx.xx'--put your Subscriber SQL 2014 IP here
                      

Select all Open in new window

                                                                                 
-- SQL Message received below: ...voilà!!! All done now – you got your 2008 DBA database replicated to SQL 2014 IP ’xx.xxx.xxx.xx'
                                                                       
Job 'SQL2008-DBA-DBA2BI2-xxx.xx.xxx.xxx-16' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
 
 
And you’re ALL done – check Distribution agent in SSMS Replication monitor to make sure there are no errors and remember to switch back the “Distribution clean up: distribution” Publisher job
To its initial schedule – 10/15 minutes cleanup or so for the database T-Log file to not grow too big.
 
9.  IF the database has ANY TIMESTAMP COLUMNS (use scripts below to check that in each database) then do the following BEFORE starting the DISTRIBUTION agent:

• Change timestamp columns to  varbinary at subscriber BEFORE running Distribution Agents.

SELECT name FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'TableHasTimestamp') = 1
                      SELECT name FROM SYS.TABLES  WHERE object_id IN (select object_id FROM SYS.COLUMNS WHERE system_type_id = 189)
                      

Select all Open in new window

• In replication Monitor:check Subscriber Properties and change it to use SQL Login “replication”
• In replication Monitor:check Distribution Agent and flip to SA
• In replication Monitor:start Distribution agent.
• ENABLE “Distribution clean up: distribution” SQL Job on Publisher.

 
Note: I did NOT had the issue below but if you get it this is the “fix” - maybe I did it already long ago when I setup our BI2 SQL 2014 server.
For replication from lower versions like SQL 2008 MUST do the following as the error below will occur when Distribution to Subscriber agent is starting.
We MUST create “C:\Program Files\Microsoft SQL Server\100\COM” folder and give “everyone” access on the Subscriber that does not have it!! – both/ALL nodes please if its a SQL cluster…
 
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000013EF000065DA000500000000, Command ID: 1)
Error messages:
The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)
 
Repeat above process for all replicated databases
https://msdn.microsoft.com/en-us/library/ms151705(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms147834(v=sql.105).aspx