Shulou(Shulou.com)05/31 Report--

This article mainly introduces how SQL Server 2012 is downgraded to 2008R2, which is very detailed and has certain reference value. Interested friends must finish reading it!

One. Background

Some time ago, due to the compatibility of the cluster server, a hospital needed to reduce the database from 2012 to 2008R2, so it decided to temporarily switch the database to the mirror server and turn on the DML cache at the application level in order to achieve non-downtime degradation.

Since 2012 backups cannot be directly restored to 2008R2 (MSSQL database restore high > low incompatible aspphp.online), other features such as replication cannot be used, and OGG is not suitable for full library migration, it is decided to use import and export to downgrade.

II. Steps

1. Export database object schema (right-click database-task-generate script)

It should be noted that in the advanced scripting options, you need to select the full-library script generated for the 2008R2 version, and select true.

two。 Execute the above script on the target 2008R2 library to observe the error reports, many of which are normal errors because there is no data yet.

3. You need to select the option to generate all logins, but the newly created logins and passwords are random, so you need to get the passwords for each login in advance. At the same time, because

The generated script automatically disables the login, so you need to unlock each login.

4. Export data:

Do not select a view when exporting data, because the view has been built in the script in step 1, and because the table structure has been built, the import tool will by default choose to insert data into the existing table.

It takes a long time to import data, and due to server resource bottlenecks, it is better to import 50 tables at a time, depending on the size of each table. Another advantage of batching is that you can see the import progress of each table while preventing it from being re-imported every time it goes wrong, wasting a lot of time.

III. Summary

1. Tables with identity self-incrementing columns need to start identity insertion

If there are few related tables, you can query the identity keyword in the schema script in the first step to find the tables that need to turn on identity insertion, and if many need to be checked one by one.

2.SqlServer collation error, resulting in data can not be imported, so be careful to choose a consistent collation when building the database.

The sorting rules are as follows:

Instance-level collation, which affects the default collation of new databases. To change the collation at the instance level, you need to delete all databases, stop the instance, change it, and then import the data back.

Database-level collation, inherited from the instance collation, can be customized to use alter database collate Chinese_PRC_CI_AS

The above is all the content of the article "how to downgrade SQL Server 2012 to 2008R2". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.