SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Init From Backup


Init From Backup

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11359 Visits: 2730
Comments posted to this topic are about the item Init From Backup

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
meird
meird
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 102
Great article, thanks!
One comment, though, which can be useful:
You mentioned the problem when you want to manually init the subscriber. So you have to stop all users, init and then get them online again.
But even in 2000, you could do it with much shorter downtime:
1. Stop users
2. Backup the database
3. Create publication and (!) subscription
4. Disable all replication agents
5. Now you can allow users to work again!
6. Whenever you can, slowly copy your backup over the WAN and restore it
7. Enable all replication agents

This way the replication will not try to copy any row until the destination is prepared. But it will store the changes in the log (you can let the log reader work so it will be stored in the distribution db instead, that way the log will be cleaned up).



dbaker-620086
dbaker-620086
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 196
your paper [I can't say "article"!] notes BULK INSERT (table-by-table) is an exercise in slow ...

one can create/amend a replication pub/sub via

1. the replication wizard from SSMS

2. subsequently modify the config using SSMS property editor [as you do in your paper]

3. use SSMS to script out the pub+sub into a .SQL file (drop then append create)
- and edit [lots] to make it readable, have @variables instead of hardwired strings etc

where #3 is what I suspect most DBA's will do for good practices (DR, HA etc).

Within the .sql script you will find various sprocs like sp_addpublication (all documented fully in BOL) which give you even more fine control to those sprocs, e.g. by params
@sync_method
@post_snapshot_script
@enabled_for_p2p
@compress_snapshot
which are not [fully] exposed by #1 or #2 development stages.

I have not attempted the compress feature [BOL extract below], but if you had a few large tables (and didn't justify the .BAK approach in the paper) it would be worth investigating
- might be an exercise in lots faster !


[ @compress_snapshot= ] 'compress_snapshot'
Specifies that the snapshot that is written to the @alt_snapshot_folder location is to be compressed into the Microsoft CAB format. compress_snapshot is nvarchar(5), with a default of FALSE. false specifies that the snapshot will not be compressed; true specifies that the snapshot will be compressed. Snapshot files that are larger than 2 gigabytes (GB) cannot be compressed. Compressed snapshot files are uncompressed at the location where the Distribution Agent runs; pull subscriptions are typically used with compressed snapshots so that files are uncompressed at the Subscriber. The snapshot in the default folder cannot be compressed.
Jeffrey A Hatten
Jeffrey A Hatten
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 520
I am doing the same thing where I restore from a backup. After I do the restore and create the subscription everything is fine. Except that there have been changes made to the publisher database that don't seem to get to the subscriber that were done while setting up the subscriber. I manually inserted 3 rows to test this process and make sure that changes made after the backup and before the subscriber was built would get the new rows. In the replication monitor it showed 3 transactions with 3 commands but the data never made it to the subscriber. The steps I did was create the Publisher with initialize from backup set to true. I turned off the distribution cleanup job on the publisher server. Then I did a backup of the database, moved the file to the new server and did the restore. Then I created the subscription on the publisher server using sp_addsubscription on the publisher server using init from backup and the device and backup name. Then I went to the subscriber server and created the subscription. The rows never made it to the subscriber. Any transactions after that come across but not the 3 new rows. This publisher is a live database and I want to do the backup, do the restore and have replication pick up any changes in the time it takes to do the restore and get the subscriber built.
SeanNerd
SeanNerd
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 151
Andy,

Thanks for your time posting this article. I totally love this method - but I have some concerns ...

Doesn't this method have issues if the database being replicated contains triggers and/or identity columns? I specify "NOT FOR REPLICATION" in the triggers I use, but when I use this method, the triggers still fire - because, presumably, the subscriber database is in fact a real full copy of the database - not the typical modified subscriber database.

Will I need to disable all the triggers? Plus, there is no easy way I know of to disable identity columns, without dropping the column and re-adding it as a non-identity column.

Thanks.

- Sean
dbaker-620086
dbaker-620086
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 196
when you specify NFR, the associated objects will behave normally for all clients apart from the actual replication Distribution Agent itself. The DA first calls sp_MSinit_subscription_agent sproc to establish itself as that "special one" (actually writes its SPID into MSsubscription_agents system table). The database engine recognises that SPID and skips the normal IDENTITY processing if NFR attribute has been set.

There may be multiple DA's (from various different pubs) pushing into the same table [one hopes non-overlapping IDENTITY ranges!]. So you might like to verify this against sp_who2 etc
select * from MSreplication_subscriptions
select * from MSsubscription_agents

Inevitably the pub db has extra tables such as syspublications,syssubscriptions as a result of
exec sp_replicationdboption @optname=N'publish', @value=N'true'

and these tables will inevitably be embedded in the .BAK db-dump file

the target SQL200x instance may not have replication turned on so may lack lotsa sprocs that understand such tables (and clauses like KEEP_REPLICATION), so you may get odd errors on restore and have to online and make multiuser.

It is sometimes appropriate to enable repl on such targets even if you have no intention that they be Pub/Dist) but just so the sprocs get populated, and you get an easier restore ride.

experiments show that you can flush these unwanted system tables by flipping value
exec sp_replicationdboption @optname=N'publish', @value=N'true'
exec sp_replicationdboption @optname=N'publish', @value=N'false'

so I request that you try
1. BACKUP DATABASE -- at pub (c/w "NFR" and system tables)
2. RESTORE DATABASE -- at sub
3. exec sp_replicationdboption @optname=N'publish', @value=N'true'
4. exec sp_replicationdboption @optname=N'publish', @value=N'false'
5. now add the sub to the existing pub
6. check behaviour of INSERTs at pub and ditto at sub

and post results back to this forum

HTH
SeanNerd
SeanNerd
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 151
Well, it turns out that I made a mistake. NOT FOR REPLICATION was missing fro my trigger. I apologize for the error.

After properly adding it back in, the Init From Backup method succeeded and worked properly - including the trigger and identity columns - provided they had 'NOT FOR REPLICATION' in them.

I did not have to use the method above to flip the "Publish" option form true to false.

The reason this method intrigues me is because I have a client whose subscription cannot initialize. Neither we, nor Microsoft - after over a month of an open, unresolved case - was able to solve. This method may allow the initialization to succeed where the snapshots failed.

Thanks for your article, Andy and thanks dbaker (or HTH) for the follow-ups.

- Sean
DALBIR SINGH
DALBIR SINGH
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 41
After setting up replication with this option, can we make schema changes on the publisher?
ColSchmoll
ColSchmoll
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 43
Yes you should be able to make schema changes, except changing the Primary Key.

When you run, sp_addsubscription, does it at this point start restoring from the backup? Can you restore from a transaction Log backup (having made the full backup restore manually?)

If tansactions where added to the publisher DB after the backup was taken, but before sp_addsubscription is run, are these replicated to the subscription?
Nilesh Patel-258792
Nilesh Patel-258792
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 108
Hello,
I followed instructions in this article to setup updateable replication and found identity ranges are same at subscriber. If I try to insert records on subscriber it results in following error.

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK_HardwareWMI'. Cannot insert duplicate key in object 'dbo.HardwareWMI'.
The statement has been terminated.

It seems that (1) new identity range is not allocated on subscriber and (2) check constraint for identity column is not created on subscriber.
Is there any additional step necessary to setup Updateable subscriber using this method?
Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search