SQL Server database degraded from high version to low version instance _mssql

SQL Server database degraded from high version to low version instance

Because of the widespread use of SQLServer2000, many companies want to use new SQL Server to directly "detach/append" or "Backup/restore" databases and store them in different versions. The problem of version incompatibility is often encountered. A few days ago I met a database that was backed up from my local 2008r2 to 2008 times wrong:

From the Run version 10.50.2500 (2008r2 is 10.50) and 10.00.1600 (2008 is 10.00) You can see this version of the incompatibility problem, most of the case, from the lower version to the high version, as long as the span is not too large, such as 2000 upgrade to 2012, will not be an error. A statement that implements a left join unless a new version of an incompatible feature such as *= is used. But as shown above, when you revert from a high version to a lower version, the problem arises and almost certainly the error.

Here are a few tips for downgrading from 2008 to 2005:

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

Step 1: Right click on the database you want to demote, click the following figure to select:

Step 2: In the dialog box, select:

Step 3: In Advanced, select the following figure:

Step 4: Save the script, and then 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 are screenshots

Step 5: Through the "task" → "Import data", the data from 2008 into the library using the script created the following figure, it is done:

Method Two: Using the system's own stored procedure implementation: sp_dbcmptlevel--To set some database behavior to be compatible with the specified SQL Server version

Here is the internal implementation code:

SET QUOTED_IDENTIFIER on set ANSI_NULLS in Go CREATE PROCEDURE sys.sp_dbcmptlevel--1997/04/15 @dbname sys  name = NULL,--database name to alter @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 @comptleve L Float (8) Declare @dbid INT--DBID of the database declare @dbsid varbinary (i)--ID of the owner of The database declare @orig_cmptlevel tinyint-original compatibility level declare @input_cmptlevel tinyint- Compatibility level passed under User, @cmptlvl80 tinyint-compatibility to SQL Server Version 8.0, @cmp Tlvl90 tinyint--Compatibility to SQL Server Version 9.0, @cmptlvl100 tinyint--Compatibility to SQL S erver Version 10.0 Select @cmptlvl80 =, @cmptlvl90 = @cmptlvl100 =--SP must be called At ADHOC level--if(@ @nestlevel > 1) Begin RAISERROR (15432,-1,-1, ' Sys.sp_dbcmptlevel ') return (1) End--If no @dbna 
  Me given, just list the valid compatibility level values. 
  
  If @dbname is null to 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 Ma 
  ster.dbo.sysdatabases WHERE name = @dbname--If @dbname not found, say 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 th e return clevel-clevel Select @input_cmptlevel = @new_cmptlevel Select @new_cmptlevel = @ori 
  G_cmptlevel-If No clevel is supplied, display and output current level. If @input_cmptlevel is null begin RAISERROR (15054,-1,-1, @orig_cmptlevel) return (0) End--If invalid Cleve L Given, print usage and return error code--' Usage:sp_dbcmptlevel [dbname [, CompatibilityLevel]] ' if @input_cmpt Level isn't in (@cmptlvl80, @cmptlvl90, @cmptlvl100) begin RAISERROR (15416,-1,-1) print ' RAISERROR (1 5048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100) return (1) End--only the SA or the dbo of @dbname can E 
  Xecute 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 REQUEST The ED 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 Anymor  E exec (@exec_stmt) Select @new_cmptlevel = @input_cmptlevel return (0)--sp_dbcmptlevel go

Grammar

Parameters

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

[ @new_cmptlevel = ] version

The version of SQL Server to which the database is compatible. version 's data type is tinyintand the default value is NULL. The value must be one of the following values:

% = SQL Server 2000

% = SQL Server 2005

MB = SQL Server 200

Return code value

0 (Success) or 1 (failed)

Note:

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 ALTER DATABASE compatibility level instead.

Thank you for reading, I hope to help you, thank you for your support for this site!