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, Are not going to get an 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:

Step 2: In the dialog box, select:

Step 3: Select in "Advanced":

Step 4: 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:

method Two: 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:

[SQL]View Plaincopy print?

2. SET quoted_identifier on
4. SET ansi_nulls on
6. GO
8. Create procedure sys.sp_dbcmptlevel --1997/04/15
10. @dbname sysname = NULL, --database name to change
12. @new_cmptlevel tinyint = NULL OUTPUT -The new compatibility level
14. as
16. SET NOCOUNT on
20. declare @exec_stmt nvarchar (max)
22. declare @returncode int
24. declare @comptlevel float (8)
26. declare @dbid int --dbid of the database
28. DECLARE @dbsid varbinary --ID of the owner of the database
30. declare @orig_cmptlevel tinyint --Original compatibility level
32. declare @input_cmptlevel tinyint --compatibility level passed in by user
34. , @cmptlvl80 tinyint --compatibility to SQL Server Version 8.0
36. , @cmptlvl90 tinyint --compatibility to SQL Server Version 9.0
38. , @cmptlvl100 tinyint --compatibility to SQL Server Version 10.0
40. Select @cmptlvl80 =
42. @cmptlvl90 = 90,
44. @cmptlvl100 = 100
48. --SP must is called at adhoc level- -
50. if (@ @nestlevel > 1)
52. begin
54. RAISERROR (15432,-1,-1,' sys.sp_dbcmptlevel ')
56. return (1)
58. End
62. --If no @dbname given, just list the valid compatibility level values.
64. If @dbname is null
66. begin
68. RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100)
70. return (0)
72. End
76. --Verify The database name and get info
78. Select @dbid = dbid, @dbsid = sid, @orig_cmptlevel = Cmptlevel
80. From master.dbo.sysdatabases
82. where name = @dbname
86. --If @dbname not found, say so and list the databases.
88. If @dbid is null
90. begin
92. RAISERROR (15010,-1,-1, @dbname)
94. Print '
96. Select name as ' Available databases: '
98. From master.dbo.sysdatabases
100. return (1)
102. End
106. --now save the input compatibility level and initialize the return clevel
108. -- to is the current Clevel
110. Select @input_cmptlevel = @new_cmptlevel
112. Select @new_cmptlevel = @orig_cmptlevel
116. --If no clevel is supplied, display and output current level.
118. If @input_cmptlevel is null
120. begin
122. RAISERROR (15054,-1,-1, @orig_cmptlevel)
124. return (0)
126. End
130. --If invalid clevel given, print usage and return error code
132. --' usage:sp_dbcmptlevel [dbname [, CompatibilityLevel]] '
134. If @input_cmptlevel not in (@cmptlvl80, @cmptlvl90, @cmptlvl100)
136. begin
138. RAISERROR (15416,-1,-1)
140. Print '
142. RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100)
144. return (1)
146. End
150. --only the SA or the dbo of @dbname can execute the update part
152. --of this procedure sys.so check.
154. if (Not(is_srvrolemember (' sysadmin ') = 1)) and Suser_sid () <> @dbsid
156. --ALSO allow db_owner only IF DB requested are current db
158. and (@dbid <> db_id () or is_member (' db_owner ') <> 1)
160. begin
162. RAISERROR (15418,-1,-1)
164. return (1)
166. End
170. --If we ' re in a transaction, disallow this since it might make recovery impossible.
172. set implicit_transactions off
174. If @ @trancount > 0
176. begin
178. RAISERROR (15002,-1,-1,' sys.sp_dbcmptlevel ')
180. return (1)
182. End
186. Set @exec_stmt = ' ALTER DATABASE ' + QuoteName (@dbname, ' [') + ' Set compatibility_level = ' + cast (@i Nput_cmptlevel as nvarchar (+))
190. --note:database @dbname may not exist anymore
192. exec (@exec_stmt)
196. Select @new_cmptlevel = @input_cmptlevel
200. return (0) --sp_dbcmptlevel
202. GO

Grammar [SQL]View Plaincopy print?

1. sp_dbcmptlevel [[@dbname =] name]
2. [, [@new_cmptlevel =] version]

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?