Show TOC

rs_dumpdbLocate this document in the navigation structure

Initiates a coordinated database dump.

Examples
Example 1

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?'
Example 2

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.

Note

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.

In the following sample text of the dumpdb_proc stored procedure, the dump devices are hard-coded. In a production environment, it is better to manage them in a table.
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
Usage
  • 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.

    Note

    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>.

Table 1: System Variables for rs_dumpdb Function Strings

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.