set_to_hot_site

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.set_to_hot_site') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.set_to_hot_site
GO

CREATE PROCEDURE set_to_hot_site
     @dbName VARCHAR(30) = ' ',         -- the database name on both systems
     @target VARCHAR(30) = ' ',         -- the source server name
     @targetPassword VARCHAR(30) = ' ', -- sa password on source
     @debug VARCHAR(5) = 'false'        -- debug this procedure 
AS
DECLARE 
     @CMD VARCHAR(255),                 -- reusable command holder 
     @CMDstatus INT,                    -- return status of command prompt  
     @myName VARCHAR(30)                -- name of this function 

/*
  Procedure set_to_hot_site sets the database options approptiately
  for log replication. The objective is to make sure no changes are made
  to the hot site database and no non_logged operations are permitted
*/

SELECT @myName = 'admin.set_to_hot_site'

IF (@dbName <> ' ') AND (@target <> ' ') 
  BEGIN    
    IF DB_ID(@dbName) < 6
      BEGIN
        SELECT @CMD = @myName + ' cannot be used on ' + @dbName + 
                      '. Only user databases can be safely moved via dump/load.'
        RAISERROR(@CMD,1,1) WITH SETERROR
        RETURN 1
      END
    /*
    make sure the target database is read only 
    (unless it's this administrative db)
    has checkpoint on recovery disabled
    has select into/bulkcopy disabled
    has truncate log on checkpoint disabled  
    */
    IF @dbName = 'admin' -- include more if appropriate
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'read only',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'select into/bulkcopy',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
    ELSE  -- all other dbs
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'read only',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'select into/bulkcopy',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
  END
ELSE
  BEGIN
    PRINT "  usage: set_to_hot_site"
    PRINT "                [@dbname         = <database name>       ],"
    PRINT "                [@target         = <target SQL Server>   ]," 
    PRINT "                [@targetPassword = <target sa password>  ],"
    PRINT "                [@debug          = <debug -default=false>],"
    RETURN 1
  END
RETURN
GO