/**********************************************************************/
/* 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
LiteSQL2008，精简版mssql2008r3 （320个子文件） 
errorlog.1 21KB errorlog.2 15KB errorlog.3 15KB errorlog.4 17KB errorlog.5 14KB errorlog.6 12KB SQLIOSIM.COM 216KB sqlscriptupgrade.dll 4.44MB SQLDMO.dll 4.16MB msxmlsql.dll 2.61MB DBGHELP.DLL 1021KB msvcr80.dll 618KB msvcp80.dll 542KB msvcp71.dll 488KB msvcm80.dll 468KB sqlgui.dll 444KB sqlaccess.dll 416KB pfclnt80.dll 404KB sqlqry.dll 392KB msvcr71.dll 340KB objmgr.dll 308KB XPStar.DLL 294KB pfutil80.dll 268KB semsfc.dll 224KB sqlunirl.dll 177KB xmlrw.dll 162KB sqllex.dll 148KB DataCollectorController.dll 142KB batchparser.dll 127KB xmlrwbin.dll 117KB sqlmap.dll 116KB sqlboot.dll 115KB sqlsvc.dll 114KB sqsrvres.dll 98KB ATL80.dll 95KB SQLDTSSS.DLL 95KB sqlsvc.dll 92KB sqlctr100.dll 82KB DatabaseMailengine.dll 74KB SQLOLAPSS.DLL 69KB sqlagentctr100.dll 57KB SQLAgentMail.dll 55KB ODSOLE70.dll 54KB xplog70.dll 49KB sqagtres.dll 48KB w95scm.dll 48KB DatabaseMailprotocols.dll 42KB SQLPOWERSHELLSS.DLL 41KB SEMMAP.DLL 39KB SQLREPSS.DLL 36KB atxcore.dll 36KB sqlscm.dll 34KB instapi10.dll 33KB SQLCMDSS.DLL 32KB RsFxFt.dll 31KB sqlaccess.resources.dll 30KB sqlresld.dll 28KB SQLATXSS.DLL 24KB sqlscriptdowngrade.dll 24KB sqlos.dll 24KB sqlscriptdowngrade.dll 24KB odbcbcp.dll 24KB sql_engine_core_inst_keyfile.dll 23KB sql_engine_core_inst_loc_keyfile.dll 23KB sql_engine_core_inst_loc_keyfile.dll 23KB sqltoolsmailutilities.dll 21KB SQLAGENTLOG.DLL 20KB opends60.dll 19KB xpsqlbot.dll 19KB sqlresld.dll 18KB performancecounter.dll 18KB SqlResourceLoader.dll 16KB DatabaseMailEngine.resources.dll 14KB PerformanceCounter.resources.dll 12KB DatabaseMailProtocols.resources.dll 10KB SqlToolsMailUtilities.resources.dll 9KB DatabaseMail.resources.dll 9KB QueryActivityUpload.dtsx 655KB SqlTraceUpload.dtsx 367KB QueryActivityCollect.dtsx 118KB PerfCountersUpload.dtsx 112KB SqlTraceCollect.dtsx 60KB PerfCountersCollect.dtsx 57KB TSQLQueryCollect.dtsx 43KB TSQLQueryUpload.dtsx 34KB errorlog 12KB
sqlservr.exe 41.17MB LiteSQL.exe 654KB SQLIOSIM.EXE 622KB SQLAGENT.EXE 373KB profiler.exe 356KB isqlw.exe 348KB sqlmaint.exe 80KB DCEXEC.EXE 63KB xpadsi.exe 50KB DatabaseMail.exe 17KB sqlstubss.exe 16KB sqlctr.ini 132KB sqlagtctr.ini 4KB SConfig.ini 127B共 320 条
- 1
- 2
- 3
- 4
资源评论
tonyjoen2262023-11-30#完美解决问题 #运行顺畅 非常好用，直接就可以运行，不用安装其他的
QQ2862510992024-01-21资源评论 #内容与标题一致
