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

Source: Internet
Author: User
Tags microsoft sql server management studio sql server management sql server management studio

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?
  1. SET quoted_identifier on
  2. SET ansi_nulls on
  3. GO
  4. Create procedure sys.sp_dbcmptlevel --1997/04/15
  5. @dbname sysname = NULL, --database name to change
  6. @new_cmptlevel tinyint = NULL OUTPUT -The new compatibility level
  7. as
  8. SET NOCOUNT on
  9. declare @exec_stmt nvarchar (max)
  10. declare @returncode int
  11. declare @comptlevel float (8)
  12. declare @dbid int --dbid of the database
  13. DECLARE @dbsid varbinary --ID of the owner of the database
  14. declare @orig_cmptlevel tinyint --Original compatibility level
  15. declare @input_cmptlevel tinyint --compatibility level passed in by user
  16. , @cmptlvl80 tinyint --compatibility to SQL Server Version 8.0
  17. , @cmptlvl90 tinyint --compatibility to SQL Server Version 9.0
  18. , @cmptlvl100 tinyint --compatibility to SQL Server Version 10.0
  19. Select @cmptlvl80 =
  20. @cmptlvl90 = 90,
  21. @cmptlvl100 = 100
  22. --SP must is called at adhoc level- -
  23. if (@ @nestlevel > 1)
  24. begin
  25. RAISERROR (15432,-1,-1,' sys.sp_dbcmptlevel ')
  26. return (1)
  27. End
  28. --If no @dbname given, just list the valid compatibility level values.
  29. If @dbname is null
  30. begin
  31. RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100)
  32. return (0)
  33. End
  34. --Verify The database name and get info
  35. Select @dbid = dbid, @dbsid = sid, @orig_cmptlevel = Cmptlevel
  36. From master.dbo.sysdatabases
  37. where name = @dbname
  38. --If @dbname not found, say so and list the databases.
  39. If @dbid is null
  40. begin
  41. RAISERROR (15010,-1,-1, @dbname)
  42. Print '
  43. Select name as ' Available databases: '
  44. From master.dbo.sysdatabases
  45. return (1)
  46. End
  47. --now save the input compatibility level and initialize the return clevel
  48. -- to is the current Clevel
  49. Select @input_cmptlevel = @new_cmptlevel
  50. Select @new_cmptlevel = @orig_cmptlevel
  51. --If no clevel is supplied, display and output current level.
  52. If @input_cmptlevel is null
  53. begin
  54. RAISERROR (15054,-1,-1, @orig_cmptlevel)
  55. return (0)
  56. End
  57. --If invalid clevel given, print usage and return error code
  58. --' usage:sp_dbcmptlevel [dbname [, CompatibilityLevel]] '
  59. If @input_cmptlevel not in (@cmptlvl80, @cmptlvl90, @cmptlvl100)
  60. begin
  61. RAISERROR (15416,-1,-1)
  62. Print '
  63. RAISERROR (15048,-1,-1, @cmptlvl80, @cmptlvl90, @cmptlvl100)
  64. return (1)
  65. End
  66. --only the SA or the dbo of @dbname can execute the update part
  67. --of this procedure sys.so check.
  68. if (Not(is_srvrolemember (' sysadmin ') = 1)) and Suser_sid () <> @dbsid
  69. --ALSO allow db_owner only IF DB requested are current db
  70. and (@dbid <> db_id () or is_member (' db_owner ') <> 1)
  71. begin
  72. RAISERROR (15418,-1,-1)
  73. return (1)
  74. End
  75. --If we ' re in a transaction, disallow this since it might make recovery impossible.
  76. set implicit_transactions off
  77. If @ @trancount > 0
  78. begin
  79. RAISERROR (15002,-1,-1,' sys.sp_dbcmptlevel ')
  80. return (1)
  81. End
  82. Set @exec_stmt = ' ALTER DATABASE ' + QuoteName (@dbname, ' [') + ' Set compatibility_level = ' + cast (@i Nput_cmptlevel as nvarchar (+))
  83. --note:database @dbname may not exist anymore
  84. exec (@exec_stmt)
  85. Select @new_cmptlevel = @input_cmptlevel
  86. return (0) --sp_dbcmptlevel
  87. 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?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.