/**********************************************************************/
/* PRE65TO7.SQL                                                       */
/*                                                                    */
/* Modifies the tables created by PRE60TO7.SQL to make them look like */
/* the 6.5 versions of those tables.                                  */
/* Must be run after PRE60TO7.SQL.                                    */
/*                                                                    */
/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/
/**********************************************************************/

PRINT '----------------------------------'
PRINT 'Starting execution of PRE65TO7.SQL'
PRINT '----------------------------------'
go

USE msdb
go

-- Check that we have not already been run (successfully)
DECLARE @already_upgraded INT
SET NOCOUNT ON
SELECT @already_upgraded = 0
EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                              'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                              'UpgradedFrom6X',
                               @already_upgraded OUTPUT,
                              'no_output'
IF (@already_upgraded > 0)
  RAISERROR('The MSDB database has already been successfully upgraded to 7.0.', 20, 127) WITH LOG
go

-- Allow updates to system catalogs so that we can fully manipulate our system objects
EXECUTE master.dbo.sp_configure 'allow updates', 1
go
RECONFIGURE WITH OVERRIDE
go

/**************************************************************/
/* Turn 'System Object' marking ON                            */
/**************************************************************/
EXECUTE master.dbo.sp_MS_upd_sysobj_category 1
go


/**************************************************************/
/* MSwebtasks                                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating 6.5 table MSwebtasks...'
go

-- Drop the 7.0 table
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'mswebtasks')
              AND (type = 'U')))
  DROP TABLE mswebtasks
go

-- Create the 6.5 table (we'll rename it back to mswebtasks in MSDB6TO7.SQL)
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'MSwebtasks')
              AND (type = 'U')))
  DROP TABLE MSwebtasks
go

CREATE TABLE MSwebtasks
(
procname   NVARCHAR(128) NOT NULL, -- Was VARCHAR(28) in 6.5
outputfile NVARCHAR(255) NOT NULL, -- Was VARCHAR(255) in 6.5
taskstat   BIT           NOT NULL,
wparams    IMAGE         NULL
)
go

CREATE UNIQUE INDEX web_idxproc ON MSwebtasks(procname)
go

/**************************************************************/
/* SYSTASKS                                                   */
/**************************************************************/

PRINT ''
PRINT 'Modifying the 6.0 systasks table to become a 6.5 version...'
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.syscolumns
                WHERE (id = OBJECT_ID('systasks'))
                  AND (name = 'parameters')))
  ALTER TABLE msdb.dbo.systasks ADD parameters TEXT NULL

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.syscolumns
                WHERE (id = OBJECT_ID('systasks'))
                  AND (name = 'cmdexecsuccesscode')))
  ALTER TABLE msdb.dbo.systasks ADD cmdexecsuccesscode INT  NULL
go

/**************************************************************/
/* SYSALERTS                                                  */
/**************************************************************/

PRINT ''
PRINT 'Modifying the 6.0 sysalerts table to become a 6.5 version...'
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.syscolumns
                WHERE (id = OBJECT_ID('sysalerts'))
                  AND (name = 'flags')))
  ALTER TABLE msdb.dbo.sysalerts ADD flags INT NULL
go

/**************************************************************/
/* Create old 6.5 backup/restore tables then insert dummy     */
/* rows into them so that Convert won't waste time importing  */
/* data into them.                                            */
/* NOTE: We'll delete these tables in MSDB6TO7.SQL.           */
/**************************************************************/

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sysbackupdetail')
              AND (type = 'U')))
  DROP TABLE sysbackupdetail
go

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sysbackuphistory')
              AND (type = 'U')))
  DROP TABLE sysbackuphistory
go

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sysrestoredetail')
              AND (type = 'U')))
  DROP TABLE sysrestoredetail
go

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sysrestorehistory')
              AND (type = 'U')))
  DROP TABLE sysrestorehistory
go

/**************************************************************/
/* SYSBACKUPHISTORY                                           */
/**************************************************************/

PRINT ''
PRINT 'Creating [temporary] 6.5 table sysbackuphistory...'

CREATE TABLE sysbackuphistory
(
backup_id         INT             IDENTITY(1,1), -- Primary key
CONSTRAINT        pk_backup_id    PRIMARY KEY CLUSTERED (backup_id),  -- with clustered index
id_number         SMALLINT        NOT NULL,      -- Position of the backup on the volume
backup_type       SMALLINT        NOT NULL,      -- Database(1), Transaction(2), Table(3)
database_name     VARCHAR(64)     NOT NULL,      -- Database name
table_owner       VARCHAR(64)     NULL,          -- Single Table Owner
table_name        VARCHAR(64)     NULL,          -- Table name for table backups
operator_name     CHAR(30)        NOT NULL,      -- Name of person who performed the backup
backup_start      DATETIME        NOT NULL,      -- Date and time of the oldest committed transaction
backup_finish     DATETIME        NOT NULL,      -- Date and time of the last committed transaction
server_sort       SMALLINT        NOT NULL,      -- Database server sort order
server_code_page  SMALLINT        NOT NULL,      -- Database server code page
striped_backup    SMALLINT        NOT NULL,      -- Striped backup(1), non-striped backup(0)
stripe_count      SMALLINT        NOT NULL,      -- Number of devices in the stripeset
stripe_set_name   VARCHAR(64)     NULL,          -- Stripeset Name
total_backup_size INT             NOT NULL,      -- Total size of backup in bytes (not pages)
current_sequence  CHAR(14)        NOT NULL,      -- Currentsequence number
new_sequence      CHAR(14)        NULL           -- New sequence number
)
go

/**************************************************************/
/* SYSBACKUPDETAIL                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating [temporary] 6.5 table sysbackupdetail...'

CREATE TABLE sysbackupdetail
(
backup_id       INT             NOT NULL,    -- Foreign key
CONSTRAINT      fk_backup_id    FOREIGN KEY (backup_id)
REFERENCES      sysbackuphistory(backup_id), -- to sysbackuphistory
device_type     SMALLINT        NOT NULL,    -- Disk(2), Floppy(3), Tape(5) or Pipe(6)
device_name     VARCHAR(255)    NOT NULL,    -- Device or UNC Name
backup_size     INT             NOT NULL,    -- Size of backup on this device in bytes (not pages)
volume_name     VARCHAR(6)      NULL,        -- Volume label
expiration_date DATETIME        NULL         -- Expiration date for the volume
)
go

/**************************************************************/
/* SYSRESTOREHISTORY                                          */
/**************************************************************/

PRINT ''
PRINT 'Creating [temporary] 6.5 table sysrestorehistory...'

CREATE TABLE sysrestorehistory
(
restore_id           INT           IDENTITY(1,1)  -- Primary key
CONSTRAINT           pk_restore_id PRIMARY KEY CLUSTERED (restore_id),      -- with clustered index
id_number            SMALLINT      NOT NULL,      -- Position of the backup on the volume
backup_type          SMALLINT      NOT NULL,      -- Database(1), Transaction(2), Table(3)
source_database_name VARCHAR(64)   NOT NULL,      -- Source (original) database name
new_database_name    VARCHAR(64)   NOT NULL,      -- New (destination) database name
table_owner          VARCHAR(64)   NULL,          -- Single Table Owner
table_name           VARCHAR(64)   NULL,          -- Table name for table backups
operator_name        CHAR(30)      NOT NULL,      -- Name of person who performed the backup
backup_start         DATETIME      NOT NULL,      -- Date and time of the oldest committed transaction
backup_finish        DATETIME      NOT NULL,      -- Date and time of the last committed transaction
server_sort          SMALLINT      NOT NULL,      -- Database server sort order
server_code_page     SMALLINT      NOT NULL,      -- Database server code page
striped_backup       SMALLINT      NOT NULL,      -- Striped backup(1), non-striped backup(0)
stripe_count         SMALLINT      NOT NULL,      -- Number of devices in the stripeset
stripe_set_name      VARCHAR(64)   NULL,          -- Stripeset Name
total_backup_size    INT           NOT NULL,      -- Total size of backup in bytes (not pages)
current_sequence     CHAR(14)      NOT NULL,      -- Currentsequence number
new_sequence         CHAR(14)      NULL,          -- New sequence number
restore_date         DATETIME      NOT NULL       -- Date/time restore performed
)
go

/**************************************************************/
/* SYSRESTOREDETAIL                                           */
/**************************************************************/

PRINT ''
PRINT 'Creating [temporary] 6.5 table sysrestoredetail...'

CREATE TABLE sysrestoredetail
(
restore_id      INT             NOT NULL       -- Foreign key
CONSTRAINT      fk_restore_id   FOREIGN KEY (restore_id)
REFERENCES      sysrestorehistory(restore_id), -- to sysrestorehistory
device_type     SMALLINT        NOT NULL,      -- Disk(2), Floppy(3), Tape(5) or Pipe(6)
device_name     VARCHAR(255)    NOT NULL,      -- Device or UNC Name
backup_size     INT             NOT NULL,      -- Size of backup on this device in bytes (not pages)
volume_name     VARCHAR(6)      NULL,          -- Volume label
expiration_date DATETIME        NULL           -- Expiration date for the volume
)
go

/**************************************************************/
/* SYSVOLUMELABEL                                             */
/**************************************************************/

PRINT ''
PRINT 'Creating [temporary] 6.5 table sysvolumelabel...'

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sysvolumelabel')
              AND (type = 'U')))
  DROP TABLE sysvolumelabel
go

CREATE TABLE sysvolumelabel
(
label_prefix  CHAR(2) NOT NULL,
label_counter INT     NOT NULL
)
go

/**************************************************************/
/* SYSSERVERMESSAGES                                          */
/**************************************************************/

PRINT 'Creating [temporary] 6.5 table sysservermessages...'
go

IF EXISTS (SELECT * 
           FROM msdb.dbo.sysobjects 
           WHERE (type = 'U')
             AND (name = 'sysservermessages')) 
  DROP TABLE sysservermessages
go

CREATE TABLE sysservermessages 
(
error       INT          NOT NULL,
severity    SMALLINT     NOT NULL,
dlevel      SMALLINT     NOT NULL,
description VARCHAR(255) NOT NULL,
language    VARCHAR(30)  NOT NULL
)
go

/**************************************************************/
/* Insert dummy rows...                                       */
/**************************************************************/

SET IDENTITY_INSERT msdb.dbo.sysbackuphistory ON
INSERT INTO msdb.dbo.sysbackuphistory
       (backup_id,
        id_number,
        backup_type,
        database_name,
        table_owner,
        table_name,
        operator_name,
        backup_start,
        backup_finish,
        server_sort,
        server_code_page,
        striped_backup,
        stripe_count,
        stripe_set_name,
        total_backup_size,
        current_sequence,
        new_sequence)
VALUES (0,         -- backup_id
        0,         -- id_number
        0,         -- backup_type
       'master',   -- database_name
       'dbo',      -- table_owner
       'dummy',    -- table_name
       'dbo',      -- operator_name
        GETDATE(), -- backup_start
        GETDATE(), -- backup_finish
        0,         -- server_sort
        0,         -- server_code_page
        0,         -- striped_backup
        0,         -- stripe_count
        NULL,      -- stripe_set_name
        0,         -- total_backup_size
       '0',        -- current_sequence
        NULL)      -- new_sequence
SET IDENTITY_INSERT msdb.dbo.sysbackuphistory OFF

INSERT INTO msdb.dbo.sysbackupdetail
VALUES (0,     -- backup_id
        0,     -- device_type
       'null', -- device_name
        0,     -- backup_size
        NULL,  -- volume_name
        NULL)  -- expiration_date

SET IDENTITY_INSERT msdb.dbo.sysrestorehistory ON
INSERT INTO msdb.dbo.sysrestorehistory
       (restore_id,
        id_number,
        backup_type,
        source_database_name,
        new_database_name,
        table_owner,
        table_name,
        operator_name,
        backup_start,
        backup_finish,
        server_sort,
        server_code_page,
        striped_backup,
        stripe_count,
        stripe_set_name,
        total_backup_size,
        current_sequence,
        new_sequence,
        restore_date)
VALUES (0,         -- restore_id
        0,         -- id_number
        0,         -- backup_type
       'master',   -- source_database_name
       'master',   -- new_database_name
       'dbo',      -- table_owner
       'dummy',    -- table_name
       'dbo',      -- operator_name
        GETDATE(), -- backup_start
        GETDATE(), -- backup_finish
        0,         -- server_sort
        0,         -- server_code_page
        0,         -- striped_backup
        0,         -- stripe_count
        NULL,      -- stripe_set_name
        0,         -- total_backup_size
       '0',        -- current_sequence
        NULL,      -- new_sequence
        GETDATE()) -- restore_date
SET IDENTITY_INSERT msdb.dbo.sysrestorehistory OFF

INSERT INTO msdb.dbo.sysrestoredetail
VALUES (0,     -- restore_id
        0,     -- device_type
       'null', -- device_name
        0,     -- backup_size
        NULL,  -- volume_name
        NULL)  -- expiration_date
go

INSERT INTO msdb.dbo.sysvolumelabel 
       (label_prefix, label_counter) 
VALUES ('SS', 0)
go

INSERT INTO msdb.dbo.sysservermessages
       (error,
        severity,
        dlevel,
        description,
        language)
VALUES (911,
        10,
        0,
       'dummy',
       'dummy')
go

EXECUTE master.dbo.sp_configure 'allow updates', 0
go
RECONFIGURE WITH OVERRIDE
go

/**************************************************************/
/* Turn 'System Object' marking OFF                           */
/**************************************************************/
EXECUTE master.dbo.sp_MS_upd_sysobj_category 2
go

PRINT ''
PRINT '----------------------------------'
PRINT 'Execution of PRE65TO7.SQL complete'
PRINT '----------------------------------'
go
