Backup and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoft’s documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
Alexander L. Wykel
MIT Professionals
Hi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while it’s down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
“The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous version”
“Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.”
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what I’m reading it
looks like it does, could you clarify it for me please?
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:

> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== =======
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ================================================== =======
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== =======
>
>
>
>
>
>
>
|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
[vbcol=seagreen]
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while it’s down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> “The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous version”
> “Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.”
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what I’m reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======