MasterofProject

Sogou How to downgrade the SQLServer database from high to low?

label Sqlserverdata baseDBAeditionDowngrade
64489 people read comment(21) Collection report
Classification:

Due to the widespread use of SQLServer2000, a lot of companies want to use the new SQLServer, which directly [separation / add] or [backup / restore] database, in different versions of the storage. Often encounter version is not compatible with the problem. A few days ago met from my native 2008R2 on the backup of a database restored to the top 2008 times:

From the version of the operating 10.50.2500 (2008R2 is 10.50) and 10.00.1600 (2008 is 10.00) can be seen is not compatible with this version of the problem, in most cases, from the low version upgrade to version, as long as the span is not too big, such as 2000 upgrade to 2012, not how the newspaper is wrong. Unless the use of some new features such as an incompatible version of left to achieve join * = statement. But as the picture above, from a high version to a low version of the time, the problem will appear, and almost certainly will be reported to the wrong.

Here are a few tips, examples are relegated to 2005 from 2008:

Method one: the use of graphical operations (GUI), open SSMS (Server Management Studio SQL)

Step 1: right click on the database you want to downgrade, as shown below:

step2: in the dialog box:

   Step 3: select the following figure in the [advanced]:

step4: save the script, and then run the script in the SQLServer2005.

Detailed steps can be seen:Http://prog3.com/sbdm/bbs/topics/390438560? Page=1#post-394316973?In the 13 floor of the reply, there are screenshots

Step 5: [task] [import data], the data from the 2008 into the use of the script to create the library on the following chart, completed:


methodTwo: the use of the system comes with the realization of the storage process:Sp_dbcmptlevel- a database behavior is set to be compatible with the specified Server SQL version

The following is the internal implementation of the code:

QUOTED_IDENTIFIER ON SET
ANSI_NULLS ON SET
GO
Procedure sys.sp_dbcmptlevel create - 1997/04/15
Sysname @dbname = database, - name to change NULL
Tinyint @new_cmptlevel = OUTPUT the - new compatibility level NULL to change to
As
NOCOUNT on set
 
@exec_stmt nvarchar declare (max)
@returncode int declare
@comptlevel float declare (8)
@dbid int dbid - of the database declare
@dbsid varbinary declare (85) - of the owner of the ID database
@orig_cmptlevel tinyint original - compatibility level declare
@input_cmptlevel tinyint compatibility - level passed in by declare user
, tinyint compatibility - to SQL Server Version @cmptlvl80 8
, tinyint compatibility - to SQL Server Version @cmptlvl90 9
, tinyint compatibility - to SQL Server Version @cmptlvl100 10
@cmptlvl80 select = 80,
@cmptlvl90 = 90,
@cmptlvl100 = 100
 
- MUST BE CALLED AT ADHOC LEVEL SP -
If (@@nestlevel > 1)
Begin
Raiserror (15432, -1, -1,'sys.sp_dbcmptlevel')
Return (1)
End
 
- no @dbname given just, list the valid compatibility level values. If
@dbname is null if
Begin
Raiserror (15048, -1, -1, @cmptlvl80, @cmptlvl90, @cmptlvl100,)
Return (0)
End
 
- the database name and get Verify info
@dbid select = @dbsid, sid = @orig_cmptlevel, dbid = cmptlevel
Master.dbo.sysdatabases from
Name where = @dbname
 
- @dbname not found say, so and list the If databases.
@dbid is null if
Begin
Raiserror (15010, -1, -1, @dbname)
Print ''
Name as'Available databases:'select
Master.dbo.sysdatabases from
Return (1)
End
 
现在保存输入兼容级别和初始化返回C
——是目前C
选择“input_cmptlevel = @ new_cmptlevel
选择“new_cmptlevel = @ orig_cmptlevel
 
如果没有提供C、显示和输出电流水平。
如果“input_cmptlevel是空的
开始
RAISERROR(15054,1,1,“orig_cmptlevel)
返回(0)
结束
 
如果无效C,打印使用并返回错误代码
--用法:sp_dbcmptlevel [在] [,]的compatibilitylevel
如果没有“input_cmptlevel(@ @ @ cmptlvl80,cmptlvl90,cmptlvl100)
开始
RAISERROR(15416,1,1)
打印'
RAISERROR(15048,1,1,cmptlvl80 @ @ @,cmptlvl90,cmptlvl100)
返回(1)
结束
 
——只有SA或@ dbname dbo可以执行更新的一部分
——这sys.so检查程序。
如果(不(is_srvrolemember('sysadmin”)= 1))和suser_sid() <> @ dbsid
也让db_owner只有分贝的要求是当前数据库
和(@ DBID <> db_id()或is_member('db_owner”)> 1)
开始
RAISERROR(15418,1,1)
返回(1)
结束
 
如果我们在一个不允许这种交易,因为它可能会使恢复是不可能的。
implicit_transactions下集
如果@ @ trancount > 0
开始
RAISERROR(15002,1,1,'sys。sp_dbcmptlevel”)
返回(1)
结束
 
设置“exec_stmt =“数据库”+ quotename(@ dbname,“[”)+“compatibility_level = +投(@ input_cmptlevel为nvarchar(128))
 
——注:@ dbname数据库可能不存在了
exec(@ exec_stmt)
 
选择“new_cmptlevel = @ input_cmptlevel
 
返回(0)-- sp_dbcmptlevel
去
 

语法

sp_dbcmptlevel [名字] [ @北京]
【@ new_cmptlevel = ],[版本]
 



参数

@北京]名称

要为其更改兼容级别的数据库的名称。数据库名称必须符合标识符的规则。名称的数据类型为举例,默认值为空。

“new_cmptlevel =]版本

数据库要与之兼容的SQL Server的版本。版本的数据类型为TINYINT该值必须为下列值之一,默认值为空:

八十为SQL Server 2000

九十为SQL Server 2005

一百为SQL Server 2008

返回代码值

0(成功)或1(失败)


注意事项:

后续版本的微软SQL Server将删除该功能。请不要在新的开发工作中使用该功能,并尽快修改当前还在使用该功能的应用程序改为使用兼容级别数据库。

关于备份,可以看我的另外一篇文章:第一篇--第一文SQL Server备份基础

四十九
猜你在找
查看评论
*以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:920722次
    • 积分:一万一千五百一十四
    • 等级:
    • 排名:664名第
    • 原创:100篇
    • 转载:37篇
    • 译文:95篇
    • 评论:429条
    我的微博
    博客专栏
    文章分类
    Latest comments
    Quantum statistics