How to migrate a database from one SQL Server Version to an older SQL Server Version
To migrate a database from one SQL version to an older SQL Version, the usual options like backup-restore and detach-attach are not supported.
One of the options available to migrate a database with an SQL version to an older SQL version is by using the Import and Export Data Wizard.
You can find below the steps you will have to execute in order to migrate a database using this tool and other things you have to consider when doing a migration:
Steps to use Import and Export Data Wizard:
- Open the SQL Server Management Studio- Right click on the database you wish to export- Tasks - Generate Scripts and save the script for the entire database (here you can choose advanced options if you want to script also the triggers or other option you have on the database):
- Go to the version of SQL server where you wish to import the database (the older version of the current one) and execute the script created before (like this the entire structure of the database is created on this SQL Server instance)
- You can open the Import and Export Wizard https://msdn.microsoft.com/en-us/library/ms141209(v=SQL.100).aspx , which can be used like is described in the article: https://msdn.microsoft.com/en-us/library/ms140052(v=sql.100).aspx. You can start the SQL Server Import and Export Wizard from the Start menu, from SQL Server Management Studio, from Business Intelligence Development Studio, or at the command prompt. For more information, see How to: Run the SQL Server Import and Export Wizard.
- From SQL server Management Studio, connect to Database Engine, click on Databases and right click on the database you wish to import, click Tasks- Import Data or Export data;
- Open the Import and Export Data Wizard;
- On the corresponding wizard pages, select a data source and a data destination;
- Select the option ‘Copy data from one or more tables or views’;
- Select the Tables you wish to copy;
- For the tables where you have columns with the propriety ‘IDENTITY’, please select the table and then click on ‘Edit Mappings…’ and select the option ‘Enable Identity Insert’, and click OK;
- Save and run a package;
- Verify the report after executing the command to see if everything was successful;
- To verify that you have the same number of rows for all tables in both databases, please execute the following command:
use <name of the database >
execute sp_MSforeachtable 'select count(*) from ?'
- To import and export the data you have the possibility to use flat files, Excel files or SQL Server Native Client (I propose the SQL Server Native Client );
- Also for all the operations to be successful and to not receive integrity error messages (concerning Primary Keys and Foreign Keys) the person that does all the operations must know the structure of the database;
- To transfer logins and passwords between instances of SQL Server you can use the article: https://support.microsoft.com/kb/918992 ;
- To move jobs, alerts or operators you can access the following link: https://support.microsoft.com/kb/314546;
In order to apply with success the steps described above, the one who executes the steps must know the structure of the database and also all other options configured for this database. Also, the same person must be aware of any job, alert or other configurations done on the database in question.
The option presented above it is not the only one that can be used to migrate a database. You can find all the options available, in the SQL Server Books Online and you can choose the one that best fits to your needs.