Convert Database between SQL Versions

Introduction

If you need to restore a database backup from a higher version of SQL Server, then you would need to follow these steps in order to restore properly. In this example we will be showing how to backup an application in a SQL Server 2017 environment by generating a script, and restoring it into a SQL Server 2008 environment.

Example

Step 1. Navigate to SQL Server Management Studio and right-click on the database you want to back-up. Select Tasks > Generate Scripts...

Step 2. Click Next.

Step 3. Select Script entire database and all database objects and then click Next.

Step 4. Set the File Name and Path and then click on Advanced.

Step 5. Change the Script for Server Version to SQL Server 2008 and Types of data to script to Schema and data, and click OK.

Step 6. Click Next.

Step 7. Once it finishes, click Finish.

Step 8. Copy the Script file to the target database environment.

Step 9. Edit the Script file to replace ALL target database file paths with your own database environment path.

Step 10. Run the following SQL command in a command prompt as Administrator.

sqlcmd -S <target server name> -i C:\<your file here>.sql -o <output filename>

Please note, if your target database instance is not the default SQL instance, you need to use the complete ServerName\InstanceName for <target server name>.

Once completed, check SQL Server Management Studio to see if database restored correctly in the target environment.