Solved

SQL Server 2000 Merge Replication DB backup / restore options

Posted on 2008-10-09
6
571 Views
Last Modified: 2016-05-09
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
Comment
Question by:devshed
  • 2
  • 2
6 Comments
 
LVL 10

Accepted Solution

by:
TAB8 earned 500 total points
ID: 22699204
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
 

Author Comment

by:devshed
ID: 22707819
Hello TAB8,

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

Thank you for your time,
Panos
0
 
LVL 10

Expert Comment

by:TAB8
ID: 22717268
0
 

Author Comment

by:devshed
ID: 22829533
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
 
LVL 1

Expert Comment

by:marrowyung
ID: 41584725
hi,

please help on this question.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question