- Remove From My Forums
-
Question
-
Hi ,
I tried to restore database in sql server 2012 in windows 8. but getting this error , there are several files which already worked before in restoration now not working :
TITLE: Microsoft SQL Server Management Studio
——————————Restore of database ‘classifiedspak’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&LinkId=20476
——————————
BUTTONS:OK
——————————
Thank You , Shan Ali Khan
Answers
-
You are getting this error because the database you are trying to restore to is not the same from which the backups were taken. The backups were taken from different database and you are trying to restore to a different database.
You have 2 options here.
- Use WITH REPLACE while using the RESTORE command. This will overwrite the existing database and restore the database state represented by the backup files.
- Delete the database to which you are trying to restore to and restore again using RESTORE command. This will create a new database with the state represented by the backup files.
Please note that in both these options you will lose the existing data of the database you are trying to restore to.
Refer to
http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/ for more details about the error you are facing.Refer to http://msdn.microsoft.com/en-us/library/ms186858.aspx for more details about the WITH REPLACE option.
If this does not help, can you please share more details about your scenario. Like Which database was backed up? Where are those files? Which database are you trying to restore to?
Vatsalya — MSFT The views and opinions expressed herein are those of the author and do not necessarily reflect the views of Microsoft.
-
Edited by
Saturday, November 10, 2012 4:15 PM
Added reference link. -
Marked as answer by
Shan Khan
Monday, November 12, 2012 7:38 AM
- Remove From My Forums
-
Question
-
Hi ,
I tried to restore database in sql server 2012 in windows 8. but getting this error , there are several files which already worked before in restoration now not working :
TITLE: Microsoft SQL Server Management Studio
——————————Restore of database ‘classifiedspak’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&LinkId=20476
——————————
BUTTONS:OK
——————————
Thank You , Shan Ali Khan
Answers
-
You are getting this error because the database you are trying to restore to is not the same from which the backups were taken. The backups were taken from different database and you are trying to restore to a different database.
You have 2 options here.
- Use WITH REPLACE while using the RESTORE command. This will overwrite the existing database and restore the database state represented by the backup files.
- Delete the database to which you are trying to restore to and restore again using RESTORE command. This will create a new database with the state represented by the backup files.
Please note that in both these options you will lose the existing data of the database you are trying to restore to.
Refer to
http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/ for more details about the error you are facing.Refer to http://msdn.microsoft.com/en-us/library/ms186858.aspx for more details about the WITH REPLACE option.
If this does not help, can you please share more details about your scenario. Like Which database was backed up? Where are those files? Which database are you trying to restore to?
Vatsalya — MSFT The views and opinions expressed herein are those of the author and do not necessarily reflect the views of Microsoft.
-
Edited by
Saturday, November 10, 2012 4:15 PM
Added reference link. -
Marked as answer by
Shan Khan
Monday, November 12, 2012 7:38 AM
26.05.15 — 13:14
Модель восстановления — Полная. Имеется Полная резервная копия базы. При попытке восстановления выпадает ошибка System.Data.SqlClient.SqlError: Инструкцию BACKUP LOG невозможно выполнить, так как не существует резервной копии текущей базы данных. (Microsoft.SqlServer.SmoExtended)
Подскажите, что может быть?
PS Восстанавливаю через Management Studio.
1 — 26.05.15 — 13:17
Первый раз восстанавливаешь? Тогда что-то не так сделал.
2 — 26.05.15 — 13:17
Телепатирую: у вас файл не фулл бекапа, а дифф.
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) Фулл
6 — 26.05.15 — 13:35
(3)
Провёл восстановление своей базы при указанных параметрах (полная модель восстановления). Всё отлично и на MS SQL 2005, 2008, 2012.
Может быть проблема в том, что восстанавливается бэкап от старшей версии MS SQL на младшей (т.е. бэкап, сделанный в 2012 на, допустим, 2005ом)?
7 — 26.05.15 — 13:35
(1) У себя на копии делал раньше — все нормально восстанавливалось.
8 — 26.05.15 — 13:36
(6) Наоборот на 14 восстанавливаю копию, созданную на 2008, но я так делал уже.
9 — 26.05.15 — 13:39
(1)
Непонятно причём тут BACKUP LOG, если делается наоборот восстановление?
10 — 26.05.15 — 13:39
скрипт сюда выложи
11 — 26.05.15 — 13:39
(4) При восстановлении естественно.
12 — 26.05.15 — 13:39
(9) см. (3)
13 — 26.05.15 — 13:40
(5) А я говорю Дифф.
14 — 26.05.15 — 13:42
(13)
Не, при диффе должна быть другая ошибка.
(12) Выложи, пожалуйста, скриншоты.
15 — 26.05.15 — 13:42
16 — 26.05.15 — 13:46
(14) Скриншот чего, я через Мэнэджмент Студио загружаю, вот полное описание ошибки, сейчас покажу окно настройки копирования
===================================
Ошибка восстановления базы данных «Aksol_UPP». (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
Расположение программы:
в Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
в Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.Perform(ITaskExecutionContext context)
в Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)
===================================
System.Data.SqlClient.SqlError: Инструкцию BACKUP LOG невозможно выполнить, так как не существует резервной копии текущей базы данных. (Microsoft.SqlServer.SmoExtended)
——————————
Чтобы получить справку, щелкните: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476
——————————
Расположение программы:
в Microsoft.SqlServer.Management.Smo.RestorePlan.Execute()
в Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
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
21 — 26.05.15 — 13:53
(20) Вот. Наверняка эта ошибка из-за двух галок «Создать резервную копию…»).
22 — 26.05.15 — 13:54
Надо срочно отключить эти галки, так как они мешают при проведении восстановления полного бэкапа.
23 — 26.05.15 — 13:56
(22) Я пробовал…
24 — 26.05.15 — 13:58
(22) Вроде пошло, сделал кроме отключения галок еще и NORECOVERY, вышла уже другая ошибка, что база отличается. Спасибо, буду дальше пробовать.
25 — 26.05.15 — 14:00
(24) Включи «Перезаписать существующую…»
26 — 26.05.15 — 14:00
>вышла уже другая ошибка, что база отличается
у новой базы id другой поставь галку replace
27 — 26.05.15 — 14:03
(26)
Согласен. Должно помочь.
28 — 26.05.15 — 14:12
(26) (27) Спасибо!
Одинесю
29 — 26.05.15 — 14:13
(25) Спасибо!
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)
- Remove From My Forums
-
Question
-
i have .bak file downloaded from internet , and i also have istalled sql server 2012.
my problem that i can not restore this .bak file and get this error massage :
TITLE: Microsoft SQL Server Management Studio
——————————
Restore of database ‘SRO_VT_SHARD’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf’. (Microsoft.SqlServer.SmoExtended)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&LinkId=20476
— .bak file version = 661 10 50 1600 = sql server 2008R
— my sql version = Microsoft SQL Server 2012 — 11.0.2100.60 (Intel X86)
any help please
Answers
-
Hello Alaa — The message «System.Data.SqlClient.SqlError: The operating system returned the error
‘5(Access is denied.)'», itself indicating permission issues while restoring on the path «C:Program
FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA»Few Possible Solutions:
- Change your SQL Server service account to an identity that has permissions. For testing purpose you can use «Local System», restart the service and perform the restoration again. It should succeed.
- OR You can create a folder on root of C or D drive & give full control to everyone and then try restoration again by changing the path of restoration file to these drives/ folders by using WITH MOVE option or by using GUI
- OR You can talk to your server support team and ask them to give full control/ permission to SQL Server service account on the folder «C:Program
FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA» and then try your restore action again
Hope this helps !
Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue-
Marked as answer by
Thursday, August 6, 2015 9:57 AM
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:
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: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: