Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

SQL Server 2000 Merge Replication DB backup / restore options

Hello,

To continue within the context of a previous question, I want to be able to backup / restore an MS SQL 2000 upwards (2000, 2005 and 2008) database, using DMO (MSSQL 2000 version) and/or SMO (MSSQL 2005/2008 versions) library components. The merge replicated mssql 2000 (or 2005 or 2008) database is live, with many Windows Mobile clients with active subscriptions.

If I perform backups & restores programmatically, will such actions affect my merge replication setup in any way, i.e. the Windows Mobile clients holding the subscription and the MSSQL holding the publication ?  Say, I get a backup, then I delete the database (say it is corrupted) and I perform a restore on top of it or using a new name. Will this affect the already subscribed mobile client databases ?

What do you suggest as the most optimum scenario to programmatically take backups and perform restores on live merge replicated databases ? Can this be done or we need to take the database offilne somehow (how?) in order to perform the backup/restore operation ?

Please keep in mind that we use both GUIDs and Auto-increment fields for primary keys in our database replicated tables).

Thank you for your time,
Panos
0
devshed
Asked:
devshed
  • 2
  • 2
1 Solution
 
TAB8Commented:
Lets Break down this question ...
-----------------
If I perform backups & restores programmatically, will such actions affect my merge replication setup in any way, i.e. the Windows Mobile clients holding the subscription and the MSSQL holding the publication ?
Yes ..  If you restore the Publiser with an earlier version of the database, You will have to re snapshot the publication or the subscriber will allready contain higer Auto-increment numbers and you will have conflicts ..
---------------
 I perform a restore on top of it or using a new name. Will this affect the already subscribed mobile client databases ?
Yes ..   If you rename your publication databse you will have to recreate the publication as the articles within the publication refer to databse NAME then tablename ..  you will also drop and recreate all the subscriptions ... no a good Idea
-------------------
  What do you suggest as the most optimum scenario to programmatically take backups and perform restores on live merge replicated databases ?

I a nutshell ...  you can programmatically take backups and perform restores on live merge replicated databases ...   BUT after the restore you will have to mark all supscriptions for re-initialisation ..  This will drop all data at the client end and update it with the publishers data (that you just restored)



0
 
devshedAuthor Commented:
Hello TAB8,

Can I automatically mark the subscriptions for re-initialisation following a restore ? (How?)

Thank you for your time,
Panos
0
 
devshedAuthor Commented:
Hello TAB8,

Sorry for not responding earlier to your comment.

So, if I follow your advice, I just restore my DB and then I execute the SP_REINITSUBSCRIPTION on the MSSQL Server, right ?

On the client side, i.e. Windows Mobile .NET CF application, do I have to call the ReinitializeSubscription method on the replication object deleting my existing local CE database ? Is there a way for my client code to catch the error that the current subscription is invalid, so as to force the reinitialization of the local CE database (subscription) ?

Thank you for your time,
Panayotis Tzedakis.
0
 
marrowyungCommented:
hi,

please help on this question.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now