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, right-click the target database, and go to Tasks > Generate Scripts
3. Select Next.
4. Select Script entire database and all database objects and then Next.
5. Set the File Name and Path and then select 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. Select OK.
8. Select Next.
9. After the scripts have been saved or published, select Finish.
10. Copy the script file to the target database environment.
11. Edit the file to replace ALL target database file paths with your own database environment path.
12. 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>
Note: If your target database instance is not the default SQL instance, use the complete ServerName\InstanceName for <target server name>.
13. Open SSMS to see if the database has been properly restored in the target environment.