SQL Server database downgrade from high version to low version instance details, SQL Server details

SQL Server database downgrade from high version to low version instance details, SQL Server details

How to downgrade an SQL Server database from a higher version to a lower version

Currently, SQLServer2000 is widely used. Many companies want to use the new SQL Server to directly [separate/Attach] or [back up/restore] the database and store it among different versions. The version is often incompatible. A few days ago, I encountered an error when I restored a database backed up on 2008R2 to 2008:

We can see from the running versions 10.50.2500 (2008R2 is 10.50) and 10.00.1600 (2008 is 10.00) that this version is incompatible. In most cases, we can upgrade from a lower version to a higher version, as long as the span is not too large, such as 2000 to 2012, no error will be reported. Unless some incompatible features of the new version, such as * =, are used to implement the left join statement. However, as in that case, when the previous version is restored to the earlier version, the problem occurs and almost certainly reports an error.

Below are a few small suggestions. The example is to downgrade from 2008 to 2005:

Method 1: Use a graphical operation (GUI) to open SSMS (SQL Server Management Studio)

Step 1: Right-click the database you want to downgrade and select:

Step 2: In the dialog box, select:

Step 3: select:

Step 4: Save the script and run it in SQLServer2005.

Detailed steps can see: http://bbs.csdn.net/topics/390438560? Page = 1 # reply from the 13th floor in post-394316973,

Step 5: Use [task]> [import data] to import data from 2008 to the database created using the script, as shown in the following figure:

Method 2: Use the stored procedure that comes with the system to implement: sp_dbcmptlevel -- set certain database behaviors to be compatible with the specified SQL Server version

The following is the internal implementation code:

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 

Syntax

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ] 

Parameters

[ @dbname = ] name 

To change the name of a compatible database. The database name must comply with the identifier rules.NameThe data type of isSysnameThe default value is NULL.

[@ New_cmptlevel =]Version

The version of the SQL Server to which the database is compatible.VersionThe data type of isTinyintThe default value is NULL. This value must be one of the following values:

80= SQL Server 2000

90= SQL Server 2005

100= SQL Server 200

Return code value

0 (successful) or 1 (failed)

Note:

This function will be deleted in later versions of Microsoft SQL Server. Do not use this function in new development work, and modify the application that is currently using this function as soon as possible. Use the alter database compatibility level.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!