Creating a replica by exporting a subscription without data
In some cases, you might have a database that already contains all the appropriate data, but is not already configured to be a replica. For example, you might have a server that has a backup copy of an existing master, and you might want to convert that copy to a replica. If the database already has the appropriate data, then you do not want to have to discard and then refresh all that data. One solution is to export just the subscription metadata from the master, then import that into the copy of the master (that is, into the database that you want to convert to a replica). You use the EXPORT SUBSCRIPTION statement with the WITH NO DATA option to export the schema (the metadata)
Important Make sure that valid data exists on the replica before you use this option, or an application accessing the replica might use the wrong set of data.
The following procedure demonstrates exporting one or more files with no data from the master database, creating a replica database from a master database, and then loading the files that contain the metadata and publication information to the replica database by using the IMPORT statement.
You can also perform queries to see what bookmarks and publications currently exist in your system. See Retrieving bookmark information.
2 Execute the EXPORT SUBSCRIPTION statement with the WITH NO DATA option for every publication for which you need to create an export file.
Only one bookmark is required even if several publications are exported. You can export several publications to a single file by specifying the same file name.
For each EXPORT SUBSCRIPTION statement that uses the WITH NO DATA option, the metadata and history data corresponding to that publication and bookmark are added to the export file.
Steps in replica database
Perform these steps in each applicable replica database:
1 Create a backup of an existing master database, for example, by using the ADMIN COMMAND 'backup' command.
2 Start the backup database and drop all replica databases by using the DROP REPLICA statement and all publications by using the DROP PUBLICATION statement, see DROP REPLICA and DROP PUBLICATION.
3 Change the server name by using the SET SYNC NODE statement (since the database is a backup of an existing master database and the original master is currently using that server name), see SET SYNC NODE.
4 Configure the database as a replica by executing the following statements:
SET SYNC MASTER NO SET SYNC REPLICA YES
Note If you are switching the copy of a master database to be a replica, the database is already set as a master database. If you execute the statement SET SYNC REPLICA YES, then the database will be defined as a dual role (master and replica) database, instead of a dedicated replica-only database. If you want to make this database only a replica database, you must execute SET SYNC MASTER NO as well as SET SYNC REPLICA YES.
5 Register this replica with the master database.
6 Import the files that you exported from the master database (by using the IMPORT statement).
7 Refresh data from the publications in the master database by using the Advanced Replication MESSAGE or REFRESH statements.
The newly-created replica is ready for use. If this is the last replica you are creating, drop the bookmark from the master database as described inDropping bookmarks.