In order to restore a SQL database from a newer version of SQL to an older version, for example from Microsoft SQL 2019 to Microsoft SQL 2016, a Migration Script is needed due to compatibility issues.
Follow the below guide to generate a SQL Migration Script.
- When looking at the database that you would like to migrate within Microsoft SQL Server Management, right click on the database and select Tasks.
- Within the sub-menu for Tasks select Generate Scripts.
- Within the Script Wizard that opens Click on Next.
- Select the Objects you want to migrate and click Next.
- Under Set Scripting Options, click on Advanced.
- Scroll down to Types of data to script.
- Select the Schema and data option for "Types of data to script".
- Click on Ok.
- Click on Next.
- Under the summery, you can review the script details then click on Next.
- Click on Finish and a .sql file will be created.
Run the newly created .sql file against the target database you want to migrate to and this will migrate the data for you.
