set_to_primary

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

CREATE PROCEDURE set_to_primary
     @dbName VARCHAR(30) = ' ',              -- the database name 
     @source VARCHAR(30) = ' ',              -- the source server name
     @sourcePassword 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_primary sets the database options so that subsequent
  log dumps from this database will restore correctly. The objective is
  to prevent all non-logged operations.
*/

SELECT @myName = 'admin.set_to_primary'

IF     (@dbName <> ' ') AND (@source <> ' ')
  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 source database has select into/bulkcopy disabled
    truncate log on checkpoint disabled (except this admin db)  
    and no checkpoint on recovery enabled
    */
    IF @dbName = 'admin'
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -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' + @sourcePassword + ' -S' + @source + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
    ELSE
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -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' + @sourcePassword + ' -S' + @source + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 

                      ",'trunc. log on chkpt.',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -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_primary"
    PRINT "                [@dbname         = <database name>        ],"
    PRINT "                [@source         = <source SQL Server>    ]," 
    PRINT "                [@sourcePassword = <source sa password>   ],"
    PRINT "                [@debug          = <debug - default=false>],"
    RETURN 1
  END
RETURN
GO