Coping with Database Restore RRS feed

  • Question

  • In an ideal world, every database would have a very recent backup, but even then we can have some synchronization issues if a restore has to happen.

    In the application I am working on, we have a hub and spoke design.  I refer to the hub as the master database and each spoke as a satellite.  Master and Satellite are all some variant of SqlServer, either 2005 or 2008, and either Express or better, so no CE.  Synchronization is always performed from the Satellite.  We have a mechanism on our schema that defines object ownership, and the synchronization scopes have filters that leverage this, so when a Satellite performs synchronization, it always (I am going to avoid the terms 'download' and 'upload' because they are to vague) transfers data owned by the Master to the Satellite, and data owned by the Satellite to the Master.  Ultimately, each Satellite should have all of its data plus the Master's data, while the Master should have it's data plus EVERY Satellite's data.  I know, it makes the head spin.  However, all of this works great.

    BUT, suppose a database has to be restored.  Let's say it was a satellite.  After that satellite comes back on line, and we complete the PerformPostRestoreFixup process, what I observe is that any objects owned by the Master that were changed between the time of the backup and the restore re-sync and appear perfectly on the satellite, but any objects owned by the Satellite that were changed in that period of time become (for lack of a better term) 'stale' on the Master.  If the data had been created on the satellite, it not longer exists after the restore, but the Master thinks it does.  If the data had existed, but was changed, the change is lost from the point of view of the satellite, but the Master sees the change.  If the data had been deleted, it is back on the satellite, but the master still thinks it is gone.

    The same problems happen the other way around if the Master is the restored database ... it just causes problems on every satellite in that situation.

    I certainly can understand why this is so ... and I don't see this as a failing of Synchronization itself, but I am hoping there are some ideas here from people that maybe already have faced and solved this problem.  Or maybe just some creative thinkers!



    Wednesday, June 2, 2010 8:41 PM

All replies

  • Hey Kevin,

    Take a look at section "Understanding Backup and Restore for Databases That Are Synchronized"@ http://msdn.microsoft.com/en-us/library/ee617375(v=SQL.105).aspx. Since the changes on the Satellite during the time of backup and restore are not in the backup, those couldn't be restored.



    Ann Tang
    Wednesday, June 2, 2010 9:12 PM
  • Hi Ann,

    Thanks for your reply.  I'm familiar with that section.  It suggests:

    •The change was made at the server, and it was propagated to at least one client or peer server.
    After the server is restored, synchronizing with other nodes will ultimately bring the server up-to-date with these local changes that were not included in the backup. 
    Metadata is updated to handle this case properly.

    However this didn't happen, probably (almost definitely) because of the filters used in our provisioning, which limit data travel to one direction depending on ownership of the row (as I tried to describe in my original post).  You are of course correct that the restore of the database won't have the data.  Hence, I am looking for ideas on possibly getting the data from the sync partner node(s)...

    Thursday, June 3, 2010 12:08 PM
  • In your design, Is it possible that you could temporarily change the object ownership? This might work around the issues you have now.


    Ann Tang
    Friday, June 4, 2010 11:04 PM