set_to_load

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

CREATE PROCEDURE set_to_load
     @dbName VARCHAR(30) = ' ',              -- the database name
     @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 

SELECT @myName = 'admin.set_to_load'

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 not read only 
    has checkpoint on recovery disabled
    has select into/bulkcopy disabled
    has truncate log on checkpoint disabled  
    and has minimum log space
  */
  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
/* a waste of time   
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + 
                ' with TRUNCATE_ONLY' + '"'
  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
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                ",'no chkpt on recovery',true" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  END
ELSE
  BEGIN
    PRINT "  usage: set_to_load"
    PRINT "                [@dbname         = <database name>         ],"
    PRINT "                [@target         = <target SQL Server>     ]," 
    PRINT "                [@targetPassword = <target sa password>    ],"
    PRINT "                [@debug          = <debug - default=false> ],"
    RETURN 1
  END
RETURN
GO