Solved

SQL Server 2000 Merge Replication DB backup / restore options

Posted on 2008-10-09
6
557 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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now