Ошибка восстановления базы данных microsoft sqlserver management relationalenginetasks

SQL Server 2012 Troubleshooting: Restore of database failed

Одинесю

26.05.15 — 13:14

fisher

1 — 26.05.15 — 13:17

Первый раз восстанавливаешь? Тогда что-то не так сделал.

Heckfy

2 — 26.05.15 — 13:17

Телепатирую: у вас файл не фулл бекапа, а дифф.

Jaap Vduul

3 — 26.05.15 — 13:27

Наверное, включена опция «Take tail-log backup before restore»

Одинесю

4 — 26.05.15 — 13:34

(3) При создании резервной копии?

Одинесю

5 — 26.05.15 — 13:34

(2) Фулл

Alexey_Morov

6 — 26.05.15 — 13:35

Одинесю

7 — 26.05.15 — 13:35

(1) У себя на копии делал раньше — все нормально восстанавливалось.

Одинесю

8 — 26.05.15 — 13:36

(6) Наоборот на 14 восстанавливаю копию, созданную на 2008, но я так делал уже.

Alexey_Morov

9 — 26.05.15 — 13:39

leonidkorolev

10 — 26.05.15 — 13:39

скрипт сюда выложи

Jaap Vduul

11 — 26.05.15 — 13:39

(4) При восстановлении естественно.

Jaap Vduul

12 — 26.05.15 — 13:39

(9) см. (3)

Heckfy

13 — 26.05.15 — 13:40

(5) А я говорю Дифф.

Alexey_Morov

14 — 26.05.15 — 13:42

Jaap Vduul

15 — 26.05.15 — 13:42

Одинесю

16 — 26.05.15 — 13:46

Jaap Vduul

17 — 26.05.15 — 13:48

(16) см. (15)

Одинесю

18 — 26.05.15 — 13:50

Одинесю

19 — 26.05.15 — 13:50

(17) Сейчас

Одинесю

20 — 26.05.15 — 13:53

Alexey_Morov

21 — 26.05.15 — 13:53

(20) Вот. Наверняка эта ошибка из-за двух галок «Создать резервную копию…»).

Alexey_Morov

22 — 26.05.15 — 13:54

Надо срочно отключить эти галки, так как они мешают при проведении восстановления полного бэкапа.

Одинесю

23 — 26.05.15 — 13:56

(22) Я пробовал…

Одинесю

24 — 26.05.15 — 13:58

(22) Вроде пошло, сделал кроме отключения галок еще и NORECOVERY, вышла уже другая ошибка, что база отличается. Спасибо, буду дальше пробовать.

Jaap Vduul

25 — 26.05.15 — 14:00

(24) Включи «Перезаписать существующую…»

Господин ПЖ

26 — 26.05.15 — 14:00

Alexey_Morov

27 — 26.05.15 — 14:03

Одинесю

28 — 26.05.15 — 14:12

(26) (27) Спасибо!

(25) Спасибо!

SQL Server 2014 Developer SQL Server 2014 Enterprise SQL Server 2014 Enterprise Core SQL Server 2014 Standard Еще…Меньше

Perform the following commands in SQL Server Management Studio (SSMS) on the SQL Server instance you are trying to restore the database to.

Verify BAK File

RESTORE VERIFYONLY FROM DISK = 'T:DIRECTORYDATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

The backup set on file 1 is valid.

Possible Errors

If the check terminates abnormally then the cause could be because one of these checks have failed:

Checks performed by RESTORE VERIFYONLY include:

• That the backup set is complete and all volumes are readable.
• Some header fields of database pages, such as the page ID (as if it were about to write the data).
• Checksum (if present on the media).
• Checking for sufficient space on destination devices.

^{Reference: RESTORE Statements — VERIFYONLY (Transact-SQL) (Microsoft | SQL Docs)}

Check the BAK Fileheader

RESTORE HEADERONLY FROM DISK = 'T:DIRECTORYDATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

BackupName  BackupDescription     BackupType ExpirationDate Compressed Position DeviceType UserName                         ServerName    DatabaseName         DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN              LastLSN               CheckpointLSN         DatabaseBackupLSN     BackupStartDate         BackupFinishDate        ........
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ........
NULL        NULL                  1          NULL           0          1        2          NT SERVICESQLSERVERAGENT        NOTHING       StackExchange        782             2019-07-24 09:17:00.000 1688399872  568000013810400037    568000013813600001    568000013810400037    568000013524000037    2020-01-15 19:25:27.000 2020-01-15 19:25:29.000 ........

Check the Contents of the BAK File

RESTORE FILELISTONLY FROM DISK = 'T:DIRECTORYDATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

LogicalName              PhysicalName                                   Type FileGroupName    Size         MaxSize         FileId  CreateLSN           DropLSN  UniqueId                             ReadOnlyLSN         ReadWriteLSN       BackupSizeInBytes  SourceBlockSize FileGroupId LogGroupGUID  DifferentialBaseLSN  DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint 
------------------------ ---------------------------------------------- ---- ---------------- ------------ --------------- ------- ------------------- -------- ------------------------------------ ------------------- ------------------ ------------------ --------------- ----------- ------------- -------------------- ------------------------------------ ---------- --------- --------------
StackExchange            C:SQLSQL_DATAStackExchange.mdf              D    PRIMARY          2623537152   35184372080640  1       0                   0        725120E9-8DF3-4E09-9A89-94433A6EB49B 0                   0                  1687224320         4096            1           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_DefRO      C:SQLSQL_DATAStackExchangeRO.ndf            D    DEFAULTRO        5242880      35184372080640  3       89000000045300001   0        05C6BA44-123A-4749-8FB5-A0A16EF49A51 102000000014600005  90000000013100004  131072             4096            2           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 1          1         NULL
StackExchange_PUBL_SNAP  C:SQLSQL_DATAStackExchange_PUBL_SNAP.ndf    D    PUBL_SNAP_DBASE  5242880      35184372080640  4       286000001633700001  0        DCA761C4-6260-4BE3-8280-64099C98911D 0                   0                  131072             4096            3           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_log        C:SQLSQL_LOGSStackExchange_log.ldf          L    NULL             21798322176  2199023255552   2       0                   0        364124B1-081E-45C5-971C-2CD1CBBF13EC 0                   0                  0                  4096            0           NULL          0                    00000000-0000-0000-0000-000000000000 0          1         NULL

If the Steps Fail

If any of the above steps fail, then you will have to determine what the issue is. The RESTORE ... command should provide enough information to assist you in finding the root cause.

Example Solutions

If the backup file is corrupt, then you will have to find a backup file that works.
If the disk is full, then you will have to add additional space.
If all else fails, then you might have to open a case with Microsoft Support.

Best Practice

_{(emphasis mine)}

Test your backups!

You do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. It is also recommended that once you restore the backup, you perform database consistency checks via DBCC CHECKDB of the database to validate the backup media was not damaged.

^{Reference: Back Up and Restore of SQL Server Databases (Microsoft | SQL Docs)}

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:isoDB - AdventureWorksAdventureWorks2017.bak' WITH FILE = 1, 
MOVE N'AdventureWorks2017' TO N'C:Program FilesMicrosoft SQL ServerMSSQL13.SS2016MSSQLDATAAdventureWorks2017.mdf', 
MOVE N'AdventureWorks2017_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL13.SS2016MSSQLDATAAdventureWorks2017_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:isoDB - AdventureWorksAdventureWorks2017.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: