rs_dumpdb

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.

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.