Initiates a coordinated database dump.
Creates an rs_dumpdb function string that dumps the database to a specified dump device and executes a procedure to update the <rs_lastcommit> system table. This function string works best when there is only one replicate database or when all databases using the function-string class have the same dump device names.
create function string rs_dumpdb for sqlserver_derived_class output language 'dump database ?rs_destination_db!sys_raw? to pubs2_dmpdb; execute rs_update_lastcommit ?rs_origin!sys?, ?rs_origin_qid!sys?, ?rs_secondary_qid!sys?, ?rs_origin_commit_time!sys?'
This example is better suited to multiple sites and production environments than is the first example. dumpdb_proc manages the backup devices at the replicate sites. The procedure should select a backup device to use, then mark it “used” so that a subsequent dump does not overwrite the previous backup.
alter function string rs_dumpdb for sqlserver_derived_class output rpc 'execute dumpdb_proc ?rs_dump_dbname!sys?, ?rs_dump_label!sys?, ?rs_dump_timestamp!sys?, ?rs_destination_db!sys?, ?rs_origin!sys?, ?rs_origin_qid!sys?, ?rs_secondary_qid!sys?, ?rs_origin_commit_time!sys?'
The procedure uses <rs_origin>, <rs_origin_qid>, and <rs_secondary_qid> to execute rs_update_lastcommit. If the server fails after the dump is complete but before the <rs_lastcommit> system table is updated, the backup is restarted when Replication Server resumes.
There is no guarantee that the dump and the rs_update_lastcommit procedure will execute atomically, because Adaptive Server does not allow the dump command to be included in a transaction with other commands. If the <rs_lastcommit> system table is not updated successfully, an additional dump may be performed.
create proc dumpdb_proc @dump_dbname varchar(30), @dump_label varchar(30), @dump_timestamp varbinary(16), @destination_dbname varchar(30), @origin int, @origin_qid binary(36), @secondary_qid binary(36), @origin_time datetime as print 'Received a dump database command from Replication Server:' declare @message varchar(255) select @message = 'dump database ' + @dump_dbname + '. Label= '' + @dump_label + ''. Dest.db = '' + @destination_dbname + '''' print @message if @destination_dbname = 'pubs2' begin print 'issuing ''dump database pubs2.''' dump database pubs2 to pubs2_dmplog update dmp_count set d_count = d_count + 1 exec pubs2.dbo.rs_update_lastcommit @origin, @origin_qid, @secondary_qid, @origin_time end else if @destination_dbname = 'pubs3' begin print 'issuing ''dump database pubs3.''' dump database pubs3 to pubs3_dmplog update dmp_count set d_count = d_count + 1 exec pubs3.dbo.rs_update_lastcommit @origin, @origin_qid, @secondary_qid, @origin_time end
Replication Server coordinates database dumps by placing rs_dumpdb function calls in the same place in the stream of transactions distributed to each replicate Replication Server.
rs_dumpdb has function-string class scope.
Replication Server does not initialize or generate rs_dumpdb function strings for the system-provided function-string classes. You must create a function string before using a coordinated dump with Adaptive Server.
Create an rs_dumpdb function string at the Replication Server that is the primary site for the class.
To account for different dump devices at multiple replicate sites, create a stored procedure in each replicate database that performs a database dump. Then write the rs_dumpdb function string to execute the stored procedure.
The <rs_lastcommit> system table should be updated when the rs_dumpdb function string executes so that a restarted Replication Server does not perform duplicate dumps. See "rs_commit" for information about <rs_lastcommit>.
Variable Name |
Datatype |
Description |
---|---|---|
<rs_dump_dbname> |
<varchar(30)> |
The name of the database where the dump originated. |
<rs_dump_label> |
<varchar(30)> |
Label information for the dump. For Adaptive Server, this variable holds a <datetime> value that is the time the dump originated. |
<rs_dump_timestamp> |
<varbinary(16)> |
A timestamp taken when the dump started. |