技術(shù) 點
- 技術(shù)
- 點
- V幣
- 點
- 積分
- 19931
|
數(shù)據(jù)庫dba版本降級 sql server如何從高版本向低版本遷移,由于目前還廣泛使用著SQLServer2000,很多公司又想使用新的SQLServer,從而直接【分離/附加】或者【備份/還原】數(shù)據(jù)庫,在不同版本之間存放。往往就會遇到版本不兼容的問題
作者:MVP黃釗吉
前幾天遇到了從我本機2008R2上備份的一個數(shù)據(jù)庫還原到2008上面時報錯:
從運行版本10.50.2500(2008R2是10.50)和10.00.1600(2008是10.00)中可以看出這個版本不兼容問題,大部分情況下,從低版本升級到高版本,只要不是跨度太大,如2000升級到2012,都不會怎么報錯。除非使用了一些新版本不兼容的特性如*=來實現(xiàn)left join的語句。但是就像上圖那樣,從高版本還原到低版本的時候,問題就出現(xiàn)了,而且?guī)缀跻欢〞䦂箦e。
下面給出幾個小建議,例子是從2008 降級到2005:
方法一:使用圖形化操作(GUI),打開SSMS(SQL Server Management Studio)
步驟1:右鍵你要降級的數(shù)據(jù)庫,按下圖選擇:
步驟2:在對話框中選擇:
步驟3:在【高級】中選擇下圖:
步驟4:把腳本保存起來,然后在SQLServer2005中運行腳本。
步驟5:通過【任務(wù)】→【導入數(shù)據(jù)】,把數(shù)據(jù)從2008導入到使用腳本創(chuàng)建的庫上如下圖,就完成了:
方法二:使用系統(tǒng)自帶的存儲過程實現(xiàn):sp_dbcmptlevel ——將某些數(shù)據(jù)庫行為設(shè)置為與指定的 SQL Server 版本兼容下面是其內(nèi)部實現(xiàn)代碼:
[sql]
SET QUOTED_IDENTIFIER ON
- SET ANSI_NULLS ON
- GO
- create procedure sys.sp_dbcmptlevel -- 1997/04/15
- @dbname sysname = NULL, -- database name to change
- @new_cmptlevel tinyint = NULL OUTPUT -- the new compatibility level to change to
- as
- set nocount on
- declare @exec_stmt nvarchar(max)
- declare @returncode int
- declare @comptlevel float(8)
- declare @dbid int -- dbid of the database
- declare @dbsid varbinary(85) -- id of the owner of the database
- declare @orig_cmptlevel tinyint -- original compatibility level
- declare @input_cmptlevel tinyint -- compatibility level passed in by user
- ,@cmptlvl80 tinyint -- compatibility to SQL Server Version 8.0
- ,@cmptlvl90 tinyint -- compatibility to SQL Server Version 9.0
- ,@cmptlvl100 tinyint -- compatibility to SQL Server Version 10.0
- select @cmptlvl80 = 80,
- @cmptlvl90 = 90,
- @cmptlvl100 = 100
- -- SP MUST BE CALLED AT ADHOC LEVEL --
- if (@@nestlevel > 1)
- begin
- raiserror(15432,-1,-1,'sys.sp_dbcmptlevel')
- return (1)
- end
- -- If no @dbname given, just list the valid compatibility level values.
- if @dbname is null
- begin
- raiserror (15048, -1, -1, @cmptlvl80, @cmptlvl90, @cmptlvl100)
- return (0)
- end
- -- Verify the database name and get info
- select @dbid = dbid, @dbsid = sid ,@orig_cmptlevel = cmptlevel
- from master.dbo.sysdatabases
- where name = @dbname
- -- If @dbname not found, say so and list the databases.
- if @dbid is null
- begin
- raiserror(15010,-1,-1,@dbname)
- print ' '
- select name as 'Available databases:'
- from master.dbo.sysdatabases
- return (1)
- end
- -- Now save the input compatibility level and initialize the return clevel
- -- to be the current clevel
- select @input_cmptlevel = @new_cmptlevel
- select @new_cmptlevel = @orig_cmptlevel
- -- If no clevel was supplied, display and output current level.
- if @input_cmptlevel is null
- begin
- raiserror(15054, -1, -1, @orig_cmptlevel)
- return(0)
- end
- -- If invalid clevel given, print usage and return error code
- -- 'usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]'
- if @input_cmptlevel not in (@cmptlvl80, @cmptlvl90, @cmptlvl100)
- begin
- raiserror(15416, -1, -1)
- print ' '
- raiserror (15048, -1, -1, @cmptlvl80, @cmptlvl90, @cmptlvl100)
- return (1)
- end
- -- Only the SA or the dbo of @dbname can execute the update part
- -- of this procedure sys.so check.
- if (not (is_srvrolemember('sysadmin') = 1)) and suser_sid() <> @dbsid
- -- ALSO ALLOW db_owner ONLY IF DB REQUESTED IS CURRENT DB
- and (@dbid <> db_id() or is_member('db_owner') <> 1)
- begin
- raiserror(15418,-1,-1)
- return (1)
- end
- -- If we're in a transaction, disallow this since it might make recovery impossible.
- set implicit_transactions off
- if @@trancount > 0
- begin
- raiserror(15002,-1,-1,'sys.sp_dbcmptlevel')
- return (1)
- end
- set @exec_stmt = 'ALTER DATABASE ' + quotename(@dbname, '[') + ' SET COMPATIBILITY_LEVEL = ' + cast(@input_cmptlevel as nvarchar(128))
- -- Note: database @dbname may not exist anymore
- exec(@exec_stmt)
- select @new_cmptlevel = @input_cmptlevel
- return (0) -- sp_dbcmptlevel
- GO
|
|