How do I downgrade a SQL Server database from a higher version to a lower version?

How do I downgrade a SQL Server database from a higher version to a lower version?

Due to the widespread use of SQLServer2000, many companies also want to use the new SQL Server to directly "detach/Attach" or "Backup/restore" the database to be stored between different versions. The issue of incompatible versions is often encountered. A few days ago I met a database backed up from my native 2008r2. Restore to 2008 above times wrong:

This version incompatibility problem can be seen from running version 10.50.2500 (2008r2 is 10.50) and 10.00.1600 (2008 is 10.00), in most cases upgrading from a lower version to a higher version, as long as the span is not too large, such as 2000 upgrade to 2012, there will be no error. Unless some new incompatible features such as *= are used to implement a LEFT JOIN statement. But like that, when you restore from a high version to a lower version, the problem arises and almost certainly makes an error.

Here are a few tips, with the example being downgraded from 2008 to 2005:

Method One: Use the graphical operation (GUI) to open ssms (SQL Server Management Studio)

Step 1: Right-click the database you want to downgrade, press Select:

Steps2: In the dialog box, select:

Step 3: Select in "Advanced":

Steps4: Save the script and run the script in SQLServer2005. Detailed steps can be seen: http://bbs.csdn.net/topics/390438560?page=1#post-394316973 in the 13 floor of the reply, there is step 5: Through the "task" → "Import data", Importing the data from 2008 to a library created using the script is done as follows:

MethodTwo: Using the system's own stored procedure implementation:sp_dbcmptlevel--Set some database behavior to be compatible with the specified version of SQL Server

Here is its internal implementation code:

SET QUOTED_IDENTIFIER on SET ANSI_NULLS on GO CREATE PROCEDURE sys.sp_dbcmptlevel--1997/04/15 @dbname sysname = NULL,--D Atabase name to change @new_cmptlevel tinyint = NULL output--The new compatibility level to change to as SET nocount O N declare @exec_stmt nvarchar (max) DECLARE @returncodeint declare @comptlevelfloat (8) Declare @dbid int--dbid of the DAT Abase declare @dbsid varbinary--ID of the owner of the database declare @orig_cmptlevel tinyint--original Compatibil ity level declare @input_cmptlevel tinyint--compatibility level passed on 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 = @cmptlvl90 = all, @cmptlvl100 =-SP must be called at Adhoc leve L--if (@ @nestlevel > 1) Begin RAISERROR (15432,-1,-1, ' Sys.sp_dbcmptlevel ') return (1) End--if no @dbname given, jus T list the valid CompatiBility level values. If @dbname is null begin RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100) return (0) End--Verify t He 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.SYSD Atabases return (1) End-now save the input compatibility level and initialize the return Clevel – to is the current C Level Select @input_cmptlevel = @new_cmptlevel Select @new_cmptlevel = @orig_cmptlevel--If no clevel is supplied, disp Lay and output current level. If @input_cmptlevel is null begin RAISERROR (15054,-1,-1, @orig_cmptlevel) return (0) End--If invalid clevel given, PRI NT usage and return error code--' Usage:sp_dbcmptlevel [dbname [, CompatibilityLevel]] ' if @input_cmptlevel not in (@cmp TLVL80, @cmptlvl@cmptlvl100) begin RAISERROR (15416,-1,-1) print ' RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100) r Eturn (1) End-only the SA or the dbo of @dbname can execute the update part--the procedure sys.so check.  if (Not (Is_srvrolemember (' sysadmin ') = 1), and Suser_sid () <> @dbsid--ALSO allow db_owner 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 Nvarch AR)--note:database @dbname may not exist anymore exec (@exec_stmt) Select @new_cmptlevel = @input_cmptlevel Retu  RN (0)--sp_dbcmptlevel GO

Grammar

sp_dbcmptlevel [[@dbname =] name]      

Parameters

[ @dbname = ] name

The name of the database for which you want to change the compatibility level. Database names must conform to the rules for identifiers. The data type of name is sysname, and the default value is NULL.

[ @new_cmptlevel = ] version

The version of SQL Server to which the database is compatible. The data type of version is tinyint, and the default value is NULL. The value must be one of the following values:

SQL Server 2000

2005 = SQL Server

+ = SQL Server 2008

Return code value

0 (Success) or 1 (failed)

Caveats: Subsequent versions of Microsoft SQL Server will remove the feature. Do not use this feature in new development work, and modify applications that are currently using the feature as soon as possible. Use the ALTER DATABASE compatibility level instead.

For backup, you can look at my other article: first article--first SQL Server backup basics

How do I downgrade a SQL Server database from a higher version to a lower version?