Backup Database Corrupt

Question

Backup Database Corrupt

Michael Ingram

SSC Enthusiast

Points: 143
More actions
April 4, 2007 at 12:47 pm

#370121

I have a customer that has provided me with a backup of their SQL database, but when I attempt to restore the database, I get "internal consistency error". The file size of the backup appears to be correct.
One thing I did notice when I was using the Restore database wizard, was under the Options tab on the restore database box, the physical name of the .mdf doesn't look correct. It shows as database.mdf, vice database_data.mdf.
Is there anyway to run a repair or script against a database, if I can't even get it restored into SQL?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

There is a modification of the restore db command that you can run on the db backup file in the Query tool that will tell you if the db backup is good or is corrupt.... also there is one that will list what the files were from the original install.. I cannot find an example of the verify command but it is something like restore verifyonly.... in books online though....

restore filelistonly from disk = 'E:\databasea_db_200612050742.BAK'

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 2

Michael Ingram

SSC Enthusiast

Points: 143

April 5, 2007 at 8:00 am

#698801

Thanks. Will give it a shot.

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 3

I ran the RESTOREFILEONLY command and it showed me that it contained both the mdf and ldf files

I then ran the verify and it told me that it was a good backup set.

Now I'm trying to figure out a way to run a db check without having the db actually restored in SQL.

I tried to do a differential restore using my backup, but I get the Internal consistency error and then when I acknowledge the error, my database turns GRAY with (loading) in parenthesis. If I expand it, I get "No details" I leave it that way for half hour and still no joy.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

Restore verify only checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes.

I would contact the person that performed the backup and ask them to run a DBCC Check on the db and attempt to restore it somewhere on one of their systems.

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 5

Thanks, but customer was getting new server and these backups were pulled off a RAID and couldn't be restored on new server and that's why I got sent the data.

Thanks for the help.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

Try to restore with Continue_After_error, although I think the backup had to be made using the Checksum option to use it, so it might not be much help.

http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Minaz Amin SSChampion Points: 11052 More actions · Answer 7

Use RESTORE HEADERONLY option and check the backup file.

Is there any error message you receive during the restore process?

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 8

This is a different problem from the one I orig posted. When I try to do a restore, I get the following error:

There is insufficient free space on disk volume 'C:\' to create the database. The database requires 62285217792 additional bytes, while on 27378307072 bytes are available.RESTORE DATABASE is terminating abnormally.

I did a restore filelistonly and it showed my mdf was 149MB and my ldf was 62GB.

Is there a way to detach the ldf from the backup set and then recreate the ldf. I know I can do this for databases that are attached, but don't know much about manipulating backup files that haven't been restored.

Thanks

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 9

ATTACH single file...

sp_attach_single_file_db @dbname = 'tman' ,

@physname = 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\tman_data.mdf'

Michael Ingram SSC Enthusiast Points: 143 More actions · Answer 10

Thanks!!! Will give it a shot first thing in the morning.

rubes SSCrazy Points: 2058 More actions · Answer 11

I just want to emphasize the earlier comment about VERIFY not checking the validity of the data contained in the backup. More than a few DBAs have been burned by this "fine print" reality. To avoid a disaster, make sure you're doing nightly DBCC CHECKDB's. If it's critical data, I advise using an appropriate mix of FULL, Differential, and transaction log backups, providing you with more than one path to recovery. And to be absolutely sure that the data you've backed up is actually usable, set up log-shipping to a secondary server. This is a sure test to know that a tran log backup will restore.

Regards,
Rubes