Restore SQL Server 2008 database to 2005 (version downgrade)

Instance: an mssql 2008 database backup is to be restored to 2005. It was originally thought that the database compatibility level was 2005 or 2000 during the backup, and then the backup can be restored to 2005, but actually not.

I found a solution through google, which is a little troublesome, but it can still be restored perfectly.

In the later version, export the SQL scripts compatible with creating database structures in earlier versions. Execute the scripts on earlier versions, create databases and table structures, and then use the data import function, import data from a later version to a later version. During import, select "enable ID insert" for each table ".

Specifically:

Mssql manger studio open "object Resource Manager" (if not, press F8), connect to the database to be backed up, right-click the database to be backed up, and choose task> generate script

In the "select database" in the "script Wizard", select "write scripts for all objects in the selected Database"

In the next step "set script options", find the "write script for Server version" option and select the appropriate lower version ("SQL Server 2005" or 2000) (this step is important !!)

Complete the wizard and save the script to a. SQL script file.

Run the SQL script file on the target database (mssql of earlier versions.

Use the import/export function of mssql to select the expected table (usually "select all"), click all tables, click "edit ing", and enable ID insertion. Then proceed.