rs_dumpdb

Description

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.

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.

  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 4-2 lists the system variables that can be used in rs_dumpdb function strings.

See also

create function string class, rs_commit, rs_dumptran, rs_get_lastcommit

---

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF