rs_dumptran

Description

Initiates a coordinated transaction dump.

Examples

Example 1

Creates an rs_dumptran function string to execute a stored procedure named dumptran_proc. The stored procedure manages the dump devices and then executes the rs_update_lastcommit stored procedure, passing it the rs_origin, rs_origin_qid, -rs_secondary_qid, and rs_origin_commit_time parameters.

create function string rs_dumptran
 for sqlserver_derived_class
 output rpc
 'execute dumptran_proc
     ?rs_dump_dbname!sys?,
     ?rs_dump_label!sys?,
     ?rs_dump_timestamp!sys?,
     ?rs_dump_status!sys?,
     ?rs_destination_db!sys?,
     ?rs_origin!sys?,
     ?rs_origin_qid!sys?,
     ?rs_secondary_qid!sys?
     ?rs_origin_commit_time!sys?'

If the server crashes after the dump is complete but before the rs_lastcommit system table is updated, Replication Server restarts the backup.

In the following sample text of the dumptran_proc stored procedure, the dump devices are hard-coded. In a production environment, it is better to manage them in a table:

create proc dumptran_proc
   @dump_dbname varchar(30),
   @dump_label varchar(30),
   @dump_timestamp varbinary(16),
   @dump_status int,
   @destination_dbname varchar(30),
   @origin int,
   @origin_qid binary(36),
   @secondary_qid binary(36),
   @origin_time datetime
as
   print 'Received a dump transaction command from Replication Server:'
   declare @message varchar(255)
   if @dump_status = 0
   begin
      select @message = 'dump transaction ' + @dump_dbname + '. Label= '''
         + @dump_label + '''' + '. Dest.db = ''' + @destination_dbname + ''''
   end
   else if @dump_status = 1
   begin
      select @message = 'dump transaction standby ' 
         + @dump_dbname + '. Label= ''' +
         @dump_label + '''' + '. Dest.db = ''' + @destination_dbname + ''''
   end
   print @message
   if @destination_dbname = 'pubs2'
   begin
      print 'issuing ''dump transaction pubs2.'''
      if @dump_status = 0
      begin
         dump transaction pubs2 to pubs2_dmplog
      end
      else if @dump_status = 1
      begin
         dump transaction pubs2 to pubs2_dmplog with standby_access
      end
      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 transaction pubs3.'''
      if @dump_status = 0
      begin
         dump transaction pubs3 to pubs3_dmplog
      end
      else if @dump_status = 1
      begin
         dump transaction pubs3 to pubs3_dmplog with standby_access
      end
      update dmp_count set d_count = d_count + 1
      exec pubs3.dbo.rs_update_lastcommit
         @origin, @origin_qid, @secondary_qid,
         @origin_time
   end

Example 2

Alters the rs_dumptran function string that you created in the first example to execute as a remote procedure call.

alter function string rs_dumptran
 for sqlserver_derived_class
 output rpc
 'execute dumptran_proc
     ?rs_dump_dbname!sys?,
     ?rs_dump_label!sys?,
     ?rs_dump_timestamp!sys?,
     ?rs_dump_status!sys?,
     ?rs_destination_db!sys?,
     ?rs_origin!sys?,
     ?rs_origin_qid!sys?,
     ?rs_secondary_qid!sys?,
     ?rs_origin_commit_time!sys?!'

Usage

See also