如何把SQLServer数据库从高版本降级到低版本？
由于目前还广泛使用着SQLServer2000，很多公司又想使用新的SQLServer，从而直接【分离/附加】或者【备份/还原】数据库，在不同版本之间存放。往往就会遇到版本不兼容的问题。前几天遇到了从我本机2008R2上备份的一个数据库还原到2008上面时报错： 从运行版本
由于目前还广泛使用着SQLServer2000，很多公司又想使用新的SQLServer，从而直接【分离/附加】或者【备份/还原】数据库，在不同版本之间存放。往往就会遇到版本不兼容的问题。前几天遇到了从我本机2008R2上备份的一个数据库还原到2008上面时报错：
从运行版本10.50.2500（2008R2是10.50）和10.00.1600（2008是10.00）中可以看出这个版本不兼容问题，大部分情况下，从低版本升级到高版本，只要不是跨度太大，如2000升级到2012，都不会怎么报错。除非使用了一些新版本不兼容的特性如*=来实现left join的语句。但是就像上图那样，从高版本还原到低版本的时候，问题就出现了，而且几乎一定会报错。
下面给出几个小建议，例子是从2008 降级到2005：
方法一：使用图形化操作（GUI），打开SSMS(SQL Server Management Studio)
步骤1：右键你要降级的数据库，按下图选择：
步骤2：在对话框中选择：
步骤3：在【高级】中选择下图：
步骤4：把脚本保存起来，然后在SQLServer2005中运行脚本。
详细步骤可以看：http://bbs.csdn.net/topics/390438560?page=1#post-394316973 中的13楼的回复，有截图
步骤5：通过【任务】→【导入数据】，把数据从2008导入到使用脚本创建的库上如下图，就完成了：
方法二：使用系统自带的存储过程实现：sp_dbcmptlevel ——将某些数据库行为设置为与指定的 SQL Server 版本兼容
下面是其内部实现代码：
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
语法
sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]
参数
- [ @dbname = ] name
要为其更改兼容级别的数据库的名称。数据库名称必须符合标识符的规则。name 的数据类型为 sysname，默认值为 NULL。
- [ @new_cmptlevel = ] version
-
数据库要与之兼容的 SQL Server 的版本。version 的数据类型为 tinyint，默认值为 NULL。该值必须为下列值之一：
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
![]()
返回代码值
0（成功）或 1（失败）
注意事项：
后续版本的 Microsoft SQL Server 将删除该功能。请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。 改为使用 ALTER DATABASE 兼容级别。
关于备份，可以看我的另外一篇文章：第一篇——第一文 SQL Server 备份基础
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
750,000 rounds of one-on-one battle between large models, GPT-4 won the championship, and Llama 3 ranked fifth
Apr 23, 2024 pm 03:28 PM
Regarding Llama3, new test results have been released - the large model evaluation community LMSYS released a large model ranking list. Llama3 ranked fifth, and tied for first place with GPT-4 in the English category. The picture is different from other benchmarks. This list is based on one-on-one battles between models, and the evaluators from all over the network make their own propositions and scores. In the end, Llama3 ranked fifth on the list, followed by three different versions of GPT-4 and Claude3 Super Cup Opus. In the English single list, Llama3 overtook Claude and tied with GPT-4. Regarding this result, Meta’s chief scientist LeCun was very happy and forwarded the tweet and
What is the difference between mysql and sqlserver syntax
Apr 22, 2024 pm 06:33 PM
The syntax differences between MySQL and SQL Server are mainly reflected in database objects, data types, SQL statements and other aspects. Database object differences include the storage engine and how filegroups are specified, and the creation of indexes and constraints. Data type differences involve differences in numeric types, character types, and date and time types. SQL statement differences are reflected in result set limitations, data insertion, update and delete operations, etc. Other differences include how identity columns, views, and stored procedures are created. Understanding these differences is important to avoid errors when using different database systems.
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
Apple Vision Pro receives major update, visionOS 1.3 RC version released
Jul 25, 2024 pm 04:25 PM
According to news on July 24, Apple recently pushed the highly anticipated visionOS1.3RC version update to VisionPro headset users. This update marks Apple’s continued innovation and progress in the field of virtual reality. Although the official did not clearly disclose the specific content of this update, users generally expect it to include performance optimization, functional improvements, and bug fixes. The internal version number of this update is 21O771, 141 days have passed since the last update. However, due to caching issues with node server configurations in Apple's various regions, some users may experience delays in upgrades and updates. Apple recommends users to back up their data before installing updates to ensure information security. 1.VisionPro users can use the "
Where is the navicat database file?
Apr 23, 2024 am 10:57 AM
The location where the Navicat database configuration files are stored varies by operating system: Windows: The user-specific path is %APPDATA%\PremiumSoft\Navicat\macOS: The user-specific path is ~/Library/Application Support/Navicat\Linux: The user-specific path is ~/ .config/navicat\The configuration file name contains the connection type, such as navicat_mysql.ini. These configuration files store database connection information, query history, and SSH settings.
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
Recommended Office version most suitable for Win7 (perfectly compatible with Win7 system)
May 30, 2024 pm 05:24 PM
Many users are considering upgrading to the Windows 10 operating system. As Microsoft announced that it will no longer provide security updates and technical support for Windows 7. However, a considerable number of users still choose to stick to the Win7 system and look for office software suitable for this system. To help users improve office efficiency, this article will introduce several Office versions that are most suitable for Win7 systems. 1.Office2010: the perfect combination of stability and compatibility 2.Office2013: interface optimization and practical function upgrades 3.Office2016: new features of cross-platform collaboration and cloud storage 4.OfficeOnline: lightweight, free, online office anytime and anywhere 5 .WPSOffice:
