SQL Server 2008 database degraded to 2005 low version _mssql

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 tothe following:

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", Import the data from 2008 into the library created using the script as follows:

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 sysname =  
 NULL,--database name to change @new_cmptlevel tinyint = null OUTPUT--The new compatibility level to alter 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--ID of the owner of the database declare @orig_cmptl 
 Evel tinyint--original compatibility level declare @input_cmptlevel tinyint--compatibility level passed at by user , @cmptlvl80 tinyint-compatibility to SQL Server Version 8.0, @cmptlvl90 tinyint-compatibility to SQL Server Versi On 9.0, @cmptlvl100 tinyint-compatibility to SQL Server Version 10.0 Select @cmptlvl80 = @cmptlvl90 = 90, @ cmptlvl100 =--SP must is 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 to begin RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100) return (0) End-Veri FY the database name and get info Select @dbid = dbid, @dbsid = sid, @orig_cmptlevel = Cmptlevel from MASTER.DBO.SYSDA 
 tabases 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 Maste R.dbo.sysdatabases return (1) End--now save the input compatibility level and initialize the return clevel-  To is the current clevel select @input_cmptlevel = @new_cmptlevel Select @new_cmptlevel = @orig_cmptlevel--If No 
 Clevel is supplied, display and output current level.  If @input_cmptlevel is null to 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, @cm PTLVL90, @cmptlvl100) begin RAISERROR (15416,-1,-1) print ' RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @  CMPTLVL100) return (1) End--only the SA or the "dbo of the @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_cmptle vel as nvarchar (128))--Note:database @dbname may isn't exist anymore exec (@exec_stmt) Select @new_cmptlevel = @input_cmptlevel return 
 
 (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. The database name 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. Version's data type is tinyint and the default value is NULL. The value must be one of the following values:
% = SQL Server 2000
% = SQL Server 2005
MB = SQL Server 2008

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.

For a backup, you can see my other article.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.