Recently one of my teammates experienced a quite interesting issue. He was deploying new PowerShell maintenance SQL Server Agent Jobs on a new SQL Server instance. During the final test run, he noticed that some of the jobs were executing fine and one of them was waiting for a worker thread. In this blog post, I will describe what do to if you encounter a similar issue.
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.
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:
|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…
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:
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.
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.
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.
Another possibility is to use the Copy Database Wizard with the SMO transfer method.
The last solution is to use some available third-party tools that deliver Data Compare functionality.
Thanks for reading!
This month’s TSQL2sday is hosted by Steve Jones (b|t) and this time the topic is the experience we all have with database triggers, one of the most controversial database objects you can work with… Continue reading “T-SQL Tuesday #106 – Triggers Headaches or Happiness”Share it:
Today, I accidentally discovered that I’m in the TOP 30 SQL Server Bloggers of 2018 by SQLShack. This is a big surprise and I’m very happy that I received such amazing recognition. It’s a great feeling to be on such a list with all these well-known #SQLFamily members.
I couldn’t imagine more perfect timing. Speaking quite frankly, I have a hard time to get back to blogging after my vacation break and this recognition is a huge motivation for me to resume work on my posts.
I will try to publish my blog posts regularly in September, however, it may be difficult because I’m also preparing to pass the AWS Certificate exam by the end of this year. Keep fingers crossed!