Solved

SQL Server 2000 Merge Replication DB backup / restore options

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

760 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

19 Experts available now in Live!

Get 1:1 Help Now