Solved

SQL Server 2000 Merge Replication DB backup / restore options

Posted on 2008-10-09
6
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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