Restore of database failed! What now?

Restoration of a database from a backup file sometimes can be very tricky. Especially when you don’t know on what server (what environment or what SQL Server version) it was taken. Sometimes you, as a DBA, are just asked to restore database from a given backup on the pointed server. You have got a backup file, you do everything as always but for some reason, the restore operation fails.

Restore of database failed

In this blog post, I describe what is the reason behind the below error.

Error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'AdventureWorks2017' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.5026. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

So… what we can do in such case?

Let’s ask for help 🙂

First of all, as you already noticed, in the left bottom corner we have a help button. Did you try to use it? I can bet you didn’t. Let’s see how Microsoft will try to help us in our case.

As you can see the help for the first error is not available. This option in the menu is grayed out. However, the help for the second, more detailed error seems to be available. It is quite promising, isn’t it? When we click this we’re getting a new dialog with the notification that some data will be sent to Microsoft and we need to agree on that if we want to see the help for our problem.

Product Name, Product Version, and LinkId… I think I’m not afraid to share this data if that suppose to give me a solution for my problem. So what I get after clicking [Yes] button? I get nothing… New webpage opens in my browser and the only thing we get is an advertisement to buy new Surface Pro… I’m not kidding…

Additional funny thing is that Microsoft collects data about SSMS version I use: 14.0.17254.0+((SSMS_Rel_17_4).180502-0908), but why they described it as release 17.4 while I use 17.7?

Ok. Now we know that MSFT will not help us in this case.

Let’s try using T-SQL

We’re not able to restore a database using SSMS GUI so maybe it will work when using T-SQL? Let’s give it a try:

USE [master]
RESTORE DATABASE [AdventureWorks2017] 
FROM DISK = N'C:\iso\DB - AdventureWorks\AdventureWorks2017.bak' WITH FILE = 1, 
MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SS2016\MSSQL\DATA\AdventureWorks2017.mdf', 
MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SS2016\MSSQL\DATA\AdventureWorks2017_log.ldf', 
NOUNLOAD, STATS = 5
GO

No, it doesn’t work neither.

Msg 3169, Level 16, State 1, Line 2 The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.5026. 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 2 RESTORE DATABASE is terminating abnormally.

That version is incompatible with this server

So what does it mean? It simply means that database backup was taken on newer SQL Server version than the SQL Server version on which you’re trying to restore it. Unfortunately, such an operation is not supported. All SQL Servers are backward compatible and you’re always able to restore a database from a backup taken on an older version to the newer one but not vice versa.

If you want to decrypt build numbers from the error message you can use this simple cheat sheet:

You can find much more details about SQL Server builds on this page: https://sqlserverbuilds.blogspot.com/. I recommend, to add it to your bookmarks in your favorite browser. It’s invaluable when you need to quickly check SQL Server version or find latest Service Pack or Cumulative Update.

Now, armed with this knowledge, you know that this database backup file has been created on SQL Server 2017. That is the reason why I cannot restore it on SQL Server 2016.

On what SQL Server version this backup was created?

You don’t have to try to restore a database from a backup file in order to check on what version it was created. You can safely verify it using simple RESTORE HEADERONLY command.

RESTORE HEADERONLY FROM DISK = 'C:\iso\DB - AdventureWorks\AdventureWorks2017.bak';
GO

In the resultset, you will find such information as:

• Backup Name and Description
• Who created it and on what Server (Login Name, Server Name, and version)
• Database Name
• Creation Date (Start and Finish)
• and much more…

What to do when we cannot restore database from backup?

You already know that you will not be able to restore your database on the SQL Server you need. What can you do in such a situation? The solution is simple – you need to use a different database migration method. Here is a short list of few possibilities you have:

Option 1

In the case of very small databases, you can use SSMS to generate the SQL script that includes schema and data (INSERT statements). In next step, you can use this script to generate a new database on the target server.

Option 2

For bigger databases, you can generate the SQL script with the schema only and then use it to create an empty database on the target server. In the second step, you can use Import and Export Wizard or BCP command to migrate data from one database to another.

Option 3

You can also use the Export Data-Tier Application functionality to generate BACPAC file consisting of database schema and data. On the target server, you can use the Import Data-Tier Application functionality to create the new database from this file.

Do not mistake DACPAC with BACPAC. The former includes only database schema, and the latter includes database schema and data.

Option 4

Another possibility is to use the Copy Database Wizard with the SMO transfer method.

Option 5

The last solution is to use some available third-party tools that deliver Data Compare functionality.

Thanks for reading!

-Marek

Share it: