September 12, 2018 at 7:36 am
Hi. There is a lot of post about my question, but I have little different problem. We have an SQL Server 2008 R2. And full backup taken from SSMS 2017. When I want to restore it on SQL 2008, there is an error. But I can restore it on 2017. It was 2 weeks ago and I want to restore this backup and differential to it. It takes diff without problems, but only full backup give an error "restore headeronly is terminating abnormally 3013 read failure on backup device". Because only that day backup was take manually via SSMS 2017.
September 12, 2018 at 7:51 am
OK, SSMS doesn't matter. There also isn't an SSMS 2017. There are a few SSMS v17.x versions and some SSMS v16, but the tool is decoupled from the SQL Server instance version.
If you took a backup from a SQL Server 2008 R2 or earlier instance, you can restore it to the SQL Server 2008 R2 instance you have. SQL Server 2008 is different from SQL Server 2008 R2. One is v10, one is 10.5. They are not backwards compatible.
You can only restore to the same or higher level version.
September 12, 2018 at 7:52 am
farik013 - Wednesday, September 12, 2018 7:36 AMHi. There is a lot of post about my question, but I have little different problem. We have an SQL Server 2008 R2. And full backup taken from SSMS 2017. When I want to restore it on SQL 2008, there is an error. But I can restore it on 2017. It was 2 weeks ago and I want to restore this backup and differential to it. It takes diff without problems, but only full backup give an error "restore headeronly is terminating abnormally 3013 read failure on backup device". Because only that day backup was take manually via SSMS 2017.
You will not be able to restore a SQL Server 2008 R2 database to SQL Server 2008. Although both are SQL Server 2008, the R2 version has a different disk structure than 2008. You could think of SQL Server 2008 R2 as SQL Server 2010.
The version of SSMS doesn't matter.
September 12, 2018 at 7:52 am
You're trying to restore a backup from a higher version of SQL Server (2008R2 / 10.50.XXXX) to a lower version of SQL Server (2008 / 10.0.XXXX) which can not be done. The version of SSMS you used to fire off the backup is immaterial, you'd be getting similar problems if you created the backup with SSMS 2008R2 or 2008.
September 12, 2018 at 7:53 am
You can't restore a backup from 2008 R2 to 2008 -it's only forward compatible, which is why you can restore it on a 2017 instance.
September 12, 2018 at 9:38 am
Sorry for my mistake. Both servers is SQL 2008 R2. And I checked, you are right. SSMS is not matter, I take backup vis SSMS 2017 and restore it on server 2018 R2 and it restored sucessfully. This error may be on corrupted backup file?
September 12, 2018 at 11:28 am
It is entirely possible that the backup file was corrupted. A good reason to test restores periodically if not constantly.
September 12, 2018 at 12:56 pm
Is there another way to restore database? Or how can I check corruption?
When I restore in SSMS 2008, it gives me error which I said before.
But when I restore in SSMS 2017 with connecting to server 2008 R2, it doesn't give me an error.
September 12, 2018 at 3:13 pm
farik013 - Wednesday, September 12, 2018 12:56 PMIs there another way to restore database? Or how can I check corruption?
When I restore in SSMS 2008, it gives me error which I said before.
But when I restore in SSMS 2017 with connecting to server 2008 R2, it doesn't give me an error.
Copy and paste the full error message you are getting. Also check the error log to see what it may be saying as well.
September 13, 2018 at 1:47 am
With sa user there is an error in error logs:
"Cannot open backup device "\\xxxxxxxx\xxxx.bak". Operating System Error 5 (failed to retrieve text for this error. Reason: 15105). (Microsoft.SQLServer.Smo)"
But when I want to restore with my system administrator user, there is an error:
Backup,Unknown,BackupIoRequest::ReportIoError: read failure on backup device '\\xxxxxxxx\xxxx.bak'. Operating system error 13(The data is invalid.).
I think other DBA take this backup manually from PROD with his own sysadmin account and in TEST sa password different. So it gives error with permission denied. And when I want to restore with my own sysadmin account it gives second error.
I dont understand, is there a problem with permission or file corrupt?
September 13, 2018 at 5:21 am
I run query I run query restore verifyonly from disk='F:\BACKUP\ABAPROD-DOC_F0135_20180901221217_AFTERCOBFULL.bak'; and it output in SQL Server logs this message:'; and it output in SQL Server logs this message:The backup data at the end of "F:\BACKUP\ABAPROD-DOC_F0135_20180901221217_AFTERCOBFULL.bak" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media<c/> use RESTORE HEADERONLY. To determine the usability of the backup sets<c/> run RESTORE VERIFYONLY. If all of the backup sets are incomplete<c/> reformat the media using BACKUP WITH FORMAT<c/> which destroys all the backup sets.
But restore headeronly and restore verifyonly give errors
September 13, 2018 at 6:08 am
Sounds like the backup is corrupt. It happens. It's part of why validating backups is so important.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 14, 2018 at 11:03 am
farik013 - Wednesday, September 12, 2018 12:56 PMIs there another way to restore database? Or how can I check corruption?
When I restore in SSMS 2008, it gives me error which I said before.
But when I restore in SSMS 2017 with connecting to server 2008 R2, it doesn't give me an error.
You're still confusing two things here. The version of SSMS doesn't matter. You can use SSMS v 17 for any SQL Server version. You don't need to use the version that comes with SQL Server 2008 to connect to SQL Server 2008.
Second, restoring to SQL Server 2008 and restoring to SQL SErver 2008 R2 are completely different. These are not related and the R2 is a horrible name for this reason. Think of these are v 2 and v 3. You can restore v2 to v3, but not v3 to v2. Same here. You can restore a SQL Server 2008 R2 backup to another 2008 R2 instance. You CANNOT restore this on SQL Server 2008. Period. No choice here.
First, verify your version.
Second. OS error 5 is a permissions error on the file system. When you run a restore, you are NEVER running this with your account for the file permissions. The service account for the SQL Server is used to access the backup file. Your command in SSMS is a "request" for the server to go read the file. If you get this, then your service account doesn't have permissions to the file.
Third. Any sysadmin account is like any other in SQL Server. No change in permissions here, and these don't matter for the file system. The passwords don't matter.
If someone else can restore this, I suspect you are mixing versions here, but you will have to verify this run
select @@version
on the source and on your server. Use SSMS v17 for everything and then if you don't have the same versions, you can't restore down. Note in this case, v 10 and v10.5 are different verisons completely.
September 14, 2018 at 12:20 pm
farik013 - Thursday, September 13, 2018 1:47 AMWith sa user there is an error in error logs:"Cannot open backup device "\\xxxxxxxx\xxxx.bak". Operating System Error 5 (failed to retrieve text for this error. Reason: 15105). (Microsoft.SQLServer.Smo)"
But when I want to restore with my system administrator user, there is an error:
Backup,Unknown,BackupIoRequest::ReportIoError: read failure on backup device '\\xxxxxxxx\xxxx.bak'. Operating system error 13(The data is invalid.).
I think other DBA take this backup manually from PROD with his own sysadmin account and in TEST sa password different. So it gives error with permission denied. And when I want to restore with my own sysadmin account it gives second error.
I dont understand, is there a problem with permission or file corrupt?
Most likely the SQL Server service account doesn't have access to the path (which I'm presuming from your format of \\xxxxx\xxxx.bak is a network share of some sort) that the backup file resides on. It doesn't really matter which account you use to try to run the restore, if the service account doesn't have access to the share, it won't be able to read it.
The solution is to either grant the service account permission (if you're using a local account, this will be a pain,) or copy the backup file locally to the server you're trying to do the restore on.
(Edit: OK, I responded before I got to the end of the topic, Steve beat me to the answer)
September 19, 2018 at 7:16 am
I find a new problem and think this is a reason. We have a program which connect to database with SQL user and take backup everyday. Every backup with this user is good I can restore from these backups. But when I connect with my domain user, take backup and then I can't restore this backup on servers higher version. I'm sysadmin user and all privileges the same as SQL user which take backup from special program. And problem is that I can't connect with this user now, because I don't know pass and can't reset pass now. I'm confusing with this error.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply