May 14, 2015 at 9:10 pm
Hello,
We have a client in another country, I have asked them to send me the full backup in a usb, and upload ongoing log files via SFTP, something weird is, every time I restored the full backup, the log restore will fail at some point, I asked them to resend new full backup, after 3 times, I think there is something wrong rather than log file.
So I restored the full backup, bring it online, run dbcc checkdb(dbname), then I got consistency errors in database, but from customer side, dbcc checkdb doesn't return any errors, there is no faulty hardwares in client server.
1) All log files are valid
2) Database file is valid
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "D:\Data\XXXYYY_Data.mdf" is not in a valid directory.
Directory lookup for the file "E:\Log\XXXYYY_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
3)
DBCC CHECKDB WITH NO_INFOMSGS;
GO
CHECKDB found 0 allocation errors and 12 consistency errors in table 'XXX' (object ID 822747596).
CHECKDB found 0 allocation errors and 19 consistency errors in database 'XXXYYY'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXYYY).
4) SELECT * FROM msdb..suspect_pages
43 1 3202572
2 10 2015-05-15 12:07:30.907
43 1 3202573
2 10 2015-05-15 12:07:30.910
43 1 1050913
2 10 2015-05-15 12:13:57.347
43 1 4827952
2 10 2015-05-15 12:14:04.203
client is running on
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
my sql server is running on
Microsoft SQL Server 2014 - 12.0.2495.0 (X64)
Mar 31 2015 09:47:37
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
May 14, 2015 at 9:45 pm
i1888 (5/14/2015)
Hello,We have a client in another country, I have asked them to send me the full backup in a usb, and upload ongoing log files via SFTP, something weird is, every time I restored the full backup, the log restore will fail at some point, I asked them to resend new full backup, after 3 times, I think there is something wrong rather than log file.
So I restored the full backup, bring it online, run dbcc checkdb(dbname), then I got consistency errors in database, but from customer side, dbcc checkdb doesn't return any errors, there is no faulty hardwares in client server.
1) All log files are valid
2) Database file is valid
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "D:\Data\XXXYYY_Data.mdf" is not in a valid directory.
Directory lookup for the file "E:\Log\XXXYYY_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
3)
DBCC CHECKDB WITH NO_INFOMSGS;
GO
CHECKDB found 0 allocation errors and 12 consistency errors in table 'XXX' (object ID 822747596).
CHECKDB found 0 allocation errors and 19 consistency errors in database 'XXXYYY'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXYYY).
4) SELECT * FROM msdb..suspect_pages
43 1 3202572
2 10 2015-05-15 12:07:30.907
43 1 3202573
2 10 2015-05-15 12:07:30.910
43 1 1050913
2 10 2015-05-15 12:13:57.347
43 1 4827952
2 10 2015-05-15 12:14:04.203
client is running on
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
my sql server is running on
Microsoft SQL Server 2014 - 12.0.2495.0 (X64)
Mar 31 2015 09:47:37
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Quick thought, if the DBCC CHECKDB returns no errors on the client database then it is fine and the corruption is happening down the line. Ask the client to restore the full backup from the media they are sending you and run DBCC CHECKDB on the restore. If that comes out clean then the problem is almost certainly your hardware.
😎
Also make certain that DBCC is run with
DBCC CheckDB ('[database name]') WITH NO_INFOMSGS, ALL_ERRORMSGS;
May 15, 2015 at 2:34 am
in addition to the above, do you have a sql 2008r2 server you can restore the backup to to rule out version difference being the problem?
May 15, 2015 at 5:59 am
It is possible for the backup itself to become corrupted. I'm with Eirikur, test it on the client side first to see if they get the same issue.
"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
May 15, 2015 at 8:35 am
i1888 (5/14/2015)
So I restored the full backup, ...
Attempting to restore this backup ...
So did you actually restore it, or just attempted to restore?
Did you use MOVE clauses to place the files in your drives?
May 19, 2015 at 1:06 am
Hi
I did build a SQLServer2008R2 in my virtual machine to restore the database, same error...
May 19, 2015 at 1:07 am
Hi
When do real full backup restore I will set move clause, when restore verifyonly, I just leave as it is.
May 19, 2015 at 1:09 am
Hi All,
I used SFTP instead of FTP to transfer the file, I can verify the files on client server and my server are identical(as checksum is same), but I cannot restore database on my server ...
Client Server:
1. Verify backup
restore verifyonly from disk = '\\BACKUP-PC\BACKUP\XXXYYY.bak'
The backup set on file 1 is valid.
2. Checksum
C:\temp>fciv.exe C:\backup\XXXYYY.bak
//
// File Checksum Integrity Verifier version 2.05.
//
ccd6073922f7cbaba50fe9ed038bcd95 C:\backup\XXXYYY.bak
C:\temp>
My server- After SFTP transferring to my server:
1.restore verifyonly from DISK = N'D:\Backups\XXXYYY.bak'
Msg 3203, Level 16, State 1, Line 7
Read on "D:\Backups\XXXYYY.bak" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 7
VERIFY DATABASE is terminating abnormally.
2. Checksum
D:\Tools>fciv.exe d:\Backups\XXXYYY.bak
//
// File Checksum Integrity Verifier version 2.05.
//
ccd6073922f7cbaba50fe9ed038bcd95 d:\backups\XXXYYY.bak
D:\Tools>
The client backup the database to another server "Backup-PC", I am not sure if this can be the cause?
I will see if they have space to do a restore on their environment.
May 19, 2015 at 1:20 am
I am doing the test at the moment.
May 20, 2015 at 8:05 pm
I managed to made a backup locally rather than backup to another server, the backup looks good, I can restore to the client sql server, and also able to restore it to my test server.
There must be something wrong of their network, but anyway, it is good to know the database itself is healthy.
If the backup was made via network to another server, one thing worry about me is the restoreverify command will think the backup is valid, but actually not...
Anyway, thanks all for you help.
May 20, 2015 at 9:24 pm
i1888 (5/20/2015)
I managed to made a backup locally rather than backup to another server, the backup looks good, I can restore to the client sql server, and also able to restore it to my test server.There must be something wrong of their network, but anyway, it is good to know the database itself is healthy.
If the backup was made via network to another server, one thing worry about me is the restoreverify command will think the backup is valid, but actually not...
Anyway, thanks all for you help.
Keep this in mine:
Backups are worthless, Restores are priceless.
The only true validation of a backup file is to restore the database from the backup. Can be to the same server under a different name or to a different server with the original name.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply