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.

SSMS GUI 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.

SSMS GUI error - get help 01

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.

SSMS GUI error - get help 02

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…

SSMS GUI error - surface

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?

SSMS version

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:

Build number SQL Server version
14.0 SQL Server 2017
13.0 SQL Server 2016
12.0 SQL Server 2014
11.0 SQL Server 2012
10.50 SQL Server 2008 R2
10.0 SQL Server 2008
9.0 SQL Server 2005
8.0 SQL Server 2000
7.0 SQL Server 7.0

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…

SQL Server RESTORE HEADERONLY

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:
FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail

T-SQL Tuesday #85 – Backup and Recovery – Maintenance Plans

tsql2sday-300x300This month’s topic for T-SQL Tuesday #85 post is: Backup and Recovery. I think this is very interesting topic and will result in many new great articles. I assume you already know what a backup is, and why it is very important to do and store backups for all your databases. The question which many database administrators ask themselves is how to do backups right? What tools to use?

There is a lot of possibilities:

In my post for this month’s T-SQL Tuesday topic I decided to describe you, how you can leverage SQL Server built-in Maintenance Plans step-by-step. Continue reading “T-SQL Tuesday #85 – Backup and Recovery – Maintenance Plans”

Share it:
FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close