I have difficulties restoring a database on a SQL Server Version 2008 R2. Sometimes the restore cancels with an error 9004 and sometimes with an error 824. The Backup was made on the same Server and contains a full backup and several transaction log backups.

Restoring the database on a different machine with a SQL Server 2014 works fine without any errors. The Problem is that I need the database on the initial SQL Server 2008 R2.

I could possibly transfer the data from the 2014 to the 2008R2 with the export / import wizard but first I would like to know why this happens? Both machines (2008R2 an 2014) are virtual machines and the datafiles are located on a SAN.

Any Ideas?

Kind Regards
Oliver.

I have difficulties restoring a database on a SQL Server Version 2008 R2. Sometimes the restore cancels with an error 9004 and sometimes with an error 824. The Backup was made on the same Server and contains a full backup and several transaction log backups.

Restoring the database on a different machine with a SQL Server 2014 works fine without any errors. The Problem is that I need the database on the initial SQL Server 2008 R2.

I could possibly transfer the data from the 2014 to the 2008R2 with the export / import wizard but first I would like to know why this happens? Both machines (2008R2 an 2014) are virtual machines and the datafiles are located on a SAN.

Any Ideas?

Kind Regards
Oliver.

Short update:

I now was able to restore the database on a third machine running SQL Server 2008R2 successfully. Next step is to detach the database there, transfer it to the originally SQL Server and attach the database. Remains the question what was wrong or what is wrong with the machine. What tests should I start to find this out? Is it a problem with the OS, the SQL Server Installation, the virtual Hardware,

Kind Regards

Oliver.

Run DBCC CHECKDB  to see there is no corruption on the db.....

824 error generally means there is some corruption in the database. Run DBCC check db on all databases in the problematic 2008 r2. Ask your system admins/storage team to check the disks/storage for this server.

check this - https://support.microsoft.com/en-us/kb/2015756

Hi

Try rebuilding all of the indexes, then update the database statistics and then perform the backup/restore process again.

On the third Server dbcc checkdb was O.K.:

Von CHECKDB wurden 0 Zuordnungsfehler und 0 Konsistenzfehler in der mydb-Datenbank gefunden.

Die DBCC-Ausfhrung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.

But detaching the database there and attaching it on the initial Server failed:

Meldung 1813, Ebene 16, Status 2, Zeile 2

Die neue mydb-Datenbank konnte nicht geffnet werden. CREATE DATABASE wird abgebrochen.

Meldung 9004, Ebene 21, Status 1, Zeile 2

Fehler beim Verarbeiten des Protokolls fr die mydb-Datenbank. Fhren Sie nach Mglichkeit eine Wiederherstellung von einer Sicherung aus. Falls keine Sicherung verfgbar ist, muss das Protokoll mglicherweise neu erstellt werden.

Sorry for the wrong language

meantime the storage admins are checking the disks.

http://www.sqlrecoverysoftware.net/blog/sql-error-1813.html. The log file seems corrupted and hence attach is failing

http://blog.consultdba.com/2011/02/msg-9001-level-21-state-1-line-1-log.html

Recovering from corrupted log is mentioned in these posts.

My guess is that you have a faulty server. The fact that it restored OK on a different server indicates this. I would as quickly as possible take precautions and be prepared for your server to fail in a more severe way any time now.

I agree with Tibor, this looks like a hardware issue on the server. Is there any entry in the SQL Errorlog during the time?

1) 824 a soft IO error. This is where the OS could read the page but SQL Server decided that the page was corrupt for example with a page checksum failure

2)  Using the WITH CHECKSUM option on a backup will also generate a checksum over the entire backup stream and store it in the backup. This means we can detect a damaged backup by recalculating the checksum and comparing it against that stored in the backup in much the same way that page checksums work.

BACKUP DATABASE broken TO DISK=c:\test.bak WITH CHECKSUM;

3)

DBCC CHECKDB (DBNAME) WITH NO_INFOMSGS, ALL_ERRORMSGS;

BACKUP DATABASE broken TO DISK=c:\sqlskills\broken2.bck


WITH CHECKSUM;

BACKUP  DATABASE broken TODISK=c:\sqlskills\broken2.bck

WITH CHECKSUM;