LiteSQL2008，精简版mssql2008r3

/**********************************************************************/ /* MSDB8TO9.SQL */ /* */ /* Upgrades 7.x, 8.x and 9.0 to 10.0 and drops all obsolete 8.x */ /* */ /* ** Copyright Microsoft, Inc. 1994 - 2008 ** All Rights Reserved. */ /**********************************************************************/ PRINT '----------------------------------------' PRINT 'Starting execution of PREINSTMSDB100.SQL' PRINT '----------------------------------------' use msdb go -- Check that we're in msdb IF (DB_NAME() <> N'msdb') RAISERROR('A problem was encountered accessing msdb. upgrade script terminating.', 20, 127) WITH LOG go CHECKPOINT go --set compatibily level to 100 sp_dbcmptlevel @dbname = 'msdb', @new_cmptlevel = '100' go -- Allow updates to system catalogs so that we can fully manipulate our system objects EXECUTE master.dbo.sp_configure N'allow updates', 1 go RECONFIGURE WITH OVERRIDE go /**************************************************************/ /* Record time of start of creates */ /**************************************************************/ SELECT start = getdate() INTO #InstMsdb go --preserve existing object permnission during upgrade --create perms table IF (NOT OBJECT_ID(N'dbo.upgrade_perms', 'U') IS NULL) BEGIN DROP TABLE dbo.upgrade_perms END -- upgrade_perms is filled with current permission of objects in MSDB -- the structure of table is: -- state_desc = GRANT|DENY -- permission_name = SELECT|EXECUTE|UPDATE ... -- object_name = grantor name -- grantee_name = grantee name CREATE TABLE dbo.upgrade_perms(state_desc nvarchar(60), permission_name sysname, object_name sysname, grantee_name sysname) CREATE INDEX indnc ON dbo.upgrade_perms(object_name) DECLARE @state_desc nvarchar(60) DECLARE @permission_name sysname DECLARE @object_name sysname DECLARE @grantee_name sysname DECLARE perms_cursor CURSOR LOCAL FOR SELECT state_desc, permission_name, OBJECT_NAME(major_id), USER_NAME(grantee_principal_id) from msdb.sys.database_permissions WHERE state_desc IS NOT NULL AND permission_name IS NOT NULL AND OBJECT_NAME(major_id) IS NOT NULL AND USER_NAME(grantee_principal_id) IS NOT NULL OPEN perms_cursor FETCH NEXT FROM perms_cursor INTO @state_desc, @permission_name, @object_name, @grantee_name WHILE (@@fetch_status = 0) BEGIN INSERT dbo.upgrade_perms(state_desc, permission_name, object_name, grantee_name) VALUES(@state_desc, @permission_name, @object_name, @grantee_name) FETCH NEXT FROM perms_cursor INTO @state_desc, @permission_name, @object_name, @grantee_name END DEALLOCATE perms_cursor go ------------------------VIEWS UPGRADE--------------------------------------- ------------------------TABLE UPGRADE--------------------------------------- --create an populate sysoriginatingservers use msdb go IF (NOT EXISTS (SELECT * --just a safe belt, this table shouldn't be in 8.x FROM msdb.dbo.sysobjects WHERE (name = N'sysoriginatingservers') AND (type = 'U'))) BEGIN PRINT '' PRINT 'Creating table sysoriginatingservers...' CREATE TABLE dbo.sysoriginatingservers ( -- There is only a single MSX server record in this table (originating_server_id = 1) -- 0 is generated by sysoriginatingservers_view and indicates the local server originating_server_id INT CONSTRAINT CK_originating_server_id_MustBe_1 CHECK (originating_server_id = 1) DEFAULT (1) UNIQUE CLUSTERED, originating_server sysname NOT NULL UNIQUE NONCLUSTERED, --Mark this record as a MSX server entry master_server bit CONSTRAINT CK_master_server_MustBe_1 CHECK (master_server = 1) DEFAULT (1) ) END go IF (NOT EXISTS (SELECT t.name FROM msdb.sys.all_columns c JOIN msdb.sys.all_objects t ON c.object_id = t.object_id WHERE c.name = 'originating_server_id' and t.name = 'sysjobs' and t.type = 'U')) BEGIN PRINT '' PRINT 'Adding column originating_server_id to table sysjobs...' --add new column 9.0 originating_server_id ALTER TABLE sysjobs WITH NOCHECK ADD originating_server_id INT NULL END go DECLARE @MSXServerName sysname DECLARE @LocalServerName sysname DECLARE @UpdateOrgServerTSQL nvarchar(MAX) SELECT @LocalServerName = UPPER(CONVERT(sysname, SERVERPROPERTY('servername'))) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'MSXServerName', @MSXServerName OUTPUT, N'no_output' SELECT @MSXServerName = LTRIM(RTRIM(UPPER(@MSXServerName))) IF (@MSXServerName = '') SELECT @MSXServerName = NULL IF (@MSXServerName IS NOT NULL) BEGIN IF (NOT EXISTS( SELECT * FROM dbo.sysoriginatingservers WHERE originating_server_id = 1 AND originating_server = @MSXServerName AND master_server = 1)) BEGIN PRINT '' PRINT 'Populate table sysoriginatingservers...' INSERT INTO sysoriginatingservers( originating_server_id, originating_server, master_server) VALUES(1, @MSXServerName, 1) END END IF (EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'sysjobs') AND (type = 'U'))) BEGIN IF (EXISTS (SELECT t.name FROM msdb.sys.all_columns c JOIN msdb.sys.all_objects t ON c.object_id = t.object_id WHERE c.name = 'originating_server' and t.name = 'sysjobs' and t.type = 'U')) BEGIN PRINT '' PRINT 'Populate new column originating_server_id of table sysjobs...' --set this column based on the value of 8.0 only column originating_server --if MSX server is NULL we come up with server name that cannot exit, a generated GUID SELECT @UpdateOrgServerTSQL = ' UPDATE sysjobs SET originating_server_id = CASE UPPER(originating_server) WHEN ''' + @LocalServerName + ''' THEN 0 --local_server_id WHEN ''' + ISNULL(@MSXServerName, CONVERT(sysname, NEWID())) + ''' THEN 1 --msx_server_id ELSE 0 --7.0 (local) or bad data END ' EXECUTE( @UpdateOrgServerTSQL) PRINT '' PRINT 'Drop column originating_server of table sysjobs...' --drop 8.0 column originating_server DROP INDEX sysjobs.nc2 ALTER TABLE sysjobs DROP COLUMN originating_server END END go --normalize 8.0 sysjobschedules into 9.0 sysschedules and sysjobschedules IF NOT EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'sysschedules') AND (type = 'U')) BEGIN --create first sysschedules table PRINT '' PRINT 'Creating table sysschedules...' CREATE TABLE dbo.sysschedules ( schedule_id INT IDENTITY PRIMARY KEY CLUSTERED, schedule_uid UNIQUEIDENTIFIER NOT NULL, originating_server_id INT NOT NULL, name sysname NOT NULL, owner_sid varbinary(85) NOT NULL, enabled INT NOT NULL, freq_type INT NOT NULL, freq_interval INT NOT NULL, freq_subday_type INT NOT NULL, freq_subday_interval INT NOT NULL, freq_relative_interval INT NOT NULL, freq_recurrence_factor INT NOT NULL, active_start_date INT NOT NULL, active_end_date INT