The best way to protect your database is to make schedule backups. You can do it with the help of SQLBackupAndFTP, SSMS, or T-SQL Commands. But when you try to restore your database you can receive some errors. In this article, we will show how to solve the next error: “The database was backed up on a server running version”. You can get this error message during the restoration process. Here is the text of the error message 3169:
Msg 3169, Level 16, State 1, Line 1 The database was backed up on a server running version 13.00.0801. That version is incompatible with this server, which is running version 12.00.4213. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Definitely, it happened because you tried to restore the SQL Server database backup file which has been made with the help of SQL Server 2016 (13.00.0801) to SQL Server 2014 (12.00.4213). It means that there is no way to downgrade from SQL Server 2016 to SQL Server 2014. You should remember that when you upgrade your SQL Server instance, the schema level for all databases also changes. That’s why you cannot restore a database with a higher schema level to the SQL Server instance with a lower schema. But is there any solution to restore such kind of backup file? Yes, below you can find step-by-step instructions on how manually export and import your data.
The database was backed up on a server running version – Solution
The first step you need to do is to create a new, empty database. You can do it in the next way:
- Open SSMS (SQL Server Management Studio) and go to the “Object Explorer”, expand “Databases” right-click a database, then select “Tasks” and choose “Generate Scripts”. Follow the steps in the wizard to script the database objects.
- Select “Script entire database and database objects” on the “Choose Objects” page.
- Select “Save scripts to a specific location” on the “Set Scripting Options” page.
- Press the “Advanced” button. Select “Schema Only” under “Types of data to script”. Select “SQL Server 2008” (or appropriate version) under “Script for server version”.
- Press the “Next” button and complete the settings.
That is all, the generated script file is ready. You can create the complete database schema without any data. Now let’s move the data. You can do it in three ways:
- Script out the data using the method we have described above. The only definition you need to do is select “Types of data to script” as “Data Only”.
- If your database is large, script out the data for every object individually one by one.
- Use BCP to export and import the data from the old server to the new server.