How to: Backup and Restore a Database (SQL Server)
This topic describes how to back up and restore a SQL Server database that is synchronized by using Sync Framework. The code in this application focuses on the following Sync Framework classes:
For information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Backup and Restore for Databases That Are Synchronized
As with any production database, a server database that is involved in synchronization should be backed up regularly. If you restore a database from a backup, Sync Framework must update synchronization metadata to address issues with changes that can occur after the backup was taken. Changes fall into three categories:
The change was made at a client or another peer server.
After the server is restored, synchronizing with other nodes will ultimately bring the server up-to-date with these remote changes.
The change was made at the server, and it was propagated to at least one client or peer server.
After the server is restored, synchronizing with other nodes will ultimately bring the server up-to-date with these local changes that were not included in the backup. Metadata is updated to handle this case properly.
The change was made at the server, but it was not propagated to any clients or peer servers.
As with other situations in which a change is not part of a backup, changes of this type cannot be restored.
Updating Metadata after Restoring a Server
For every change that occurs in a table, Sync Framework updates metadata on the server to identify the time and origin of the change. This metadata is used by other nodes to determine which rows each node requires from the server. If changes occur at the server after a backup and those changes are propagated to other nodes before a restore, the other nodes will contain metadata for each of the changed rows. After the server is restored from the backup, subsequent changes on the server might be assigned identifiers that collide with ones that the other nodes already contain. This can cause conflicts during synchronization and possible non-convergence.
To avoid these issues, Sync Framework assigns a new replica ID to the server and updates several metadata columns for each table that is synchronized.
To Restore the Server and Update Metadata
Establish exclusive access to the server database. This prevents any local operations or synchronization operations from updating data or metadata until the database is ready.
Restore the SQL Server database from a full backup, and any differential backups if appropriate.
Execute PerformPostRestoreFixup.
Enable full access to the database. At this point, local changes can occur on the server; and clients and other peer servers can synchronize with the server.
Examples
The following code example performs a backup of the server database and makes changes in the database by calling methods in the sample Utility class, and then synchronizes those changes.
The application calls a stored procedure (usp_SampleDbBackupRestore) that contains the following Transact-SQL code to back up the server database.
The same procedure also contains Transact-SQL code to restore the server database.
The following code example restores the server database and calls PerformPostRestoreFixup() to update metadata. The application then synchronizes all nodes again.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.