Convert Database between SQL Versions

 

Introduction

In this article, we will show you how to restore a backup database from a higher/newer version of SQL Server. While the example below restores a SQL Server 2017 database to a SQL Server 2008 environment, the procedure applies as long as you are going from a newer version of SQL Server to an older version.

 

 

Example

1. Open SQL Server Management Studio (SSMS) and connect to your SQL server.

2. Expand Databases and right-click the database you want to back up.

 

 

3. Select Tasks > Generate Scripts...

 

 

3. Click Next.

 

 

4. Select Script entire database and all database objects and then click Next.

 

 

5. Set the File Name and Path and then click Advanced.

 

 

6. Set the following:

 

• Script for Server Version to SQL Server 2008
• Script Object-Level Permissions to True
• Types of data to script to Schema and data

 

7. Click OK.

 

 

8. Click Next.

 

 

9. After the scripts have been saved or published, click Finish.

 

 

10. Copy the script file to the target database environment.

 

 

10. Edit the file to replace ALL target database file paths with your own database environment path.

 

 

11. Run the following SQL command in a command prompt as Administrator:

 

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

 

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

 

 

12. Open SSMS to see if the database has been properly restored in the target environment.