Failing to restore SQL Server database backup with internal consistency error
Here is one of the corruption issue that i came across on the database which was dettached after entering into suspect mode making it difficult to re-use the mdf and ldf file.
Even the database backup they had was taken from SQL 7.0 instance which had corruption. This backup was taken post the database was corrupt.
Below im sharing the information on all the steps that I tried and finally getting the backup work with minimal data loss.
Failing to restore SQL Server database backup with internal consistency error.
ERROR
======
5 percent restored.
10 percent restored.
15 percent restored.
Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for assistance.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Troubleshooting
===============
-- Customer had SQL 7 backup and trying to restore the database and failing to restore and giving some internal consistecny error.
-- It passes all the checks (Verify Header and Filelistonly) however when trying to restore.
-- CU do not have the DB anymore in production.
-- They have mdf and ldf but they were dettached after the database went into suspect mode.
-- Ran Restore headeronly from disk = backup file name, this came with below output.
BackupName BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize
--------------- ---------- ------------------ ---------- -------- ---------- ------------------------- -------------- ----------------- --------------- ---------------------------------- ----------------------
NULL 1 NULL 0 1 2 <User Name> <Servername> <DBANme> 515 2009-01-02 13:54:13.000 2516844032
-- Restore Verifyonly -- Gave Valid backup Set.
-- restore filelistonly -- Gave the result.
-- This was giving indication that the backup is good but still it was giving some inconsistency issue.
-- Looks like the Database backup was taken post the database encoutnered corruption.
-- Connected to SQL 2005 instance and try restoring the database from the backup here.
-- We saw that everytime we try to restore the DB using the back file, we saw the DB files getting created but it was failing to complete the restore.
-- Ran the restore using WITH CONTINUE_AFTER_ERROR paramter, this continued file with no errors.
-- This completed fine but it detected inconsistency error below:
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
Processed 307200 pages for database 'DBName', file 'DBName_Data' on file 1.
Processed 1 pages for database 'DBName', file 'DBName_Log' on file 1.
100 percent processed.
Damage to the backup set was detected.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 307201 pages in 142.521 seconds (17.657 MB/sec).
-- Checked SQL Server error from file.
2010-03-15 11:41:57.15 spid63 Starting up database 'DBName'.
2010-03-15 11:41:57.20 spid63 The database 'DBName' is marked RESTORING and is in a state that does not allow recovery to be run.
2010-03-15 11:41:58.79 spid63 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-03-15 11:42:02.09 spid63 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-03-15 11:42:02.24 spid63 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-03-15 11:42:32.23 spid63 Error: 3183, Severity: 16, State: 1.
2010-03-15 11:42:32.23 spid63 RESTORE detected an error on page (0:0) in database "DBName" as read from the backup set.
2010-03-15 11:44:12.56 spid63 Starting up database 'DBName'.
2010-03-15 11:44:12.58 spid63 The database 'DBName' is marked RESTORING and is in a state that does not allow recovery to be run.
2010-03-15 11:44:13.22 Backup Database was restored: Database: DBName, creation date(time): 2009/01/02(13:54:13), first LSN: 12375:571:29, last LSN: 12375:582:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\DIR\Backup\DBName_20100310_214921.bak'}). Informational message. No user action required.
-- In SSMS the status of the DB was restoring.
-- Ran --> Restore database with recovery, but failed with below error:
Server: Msg 4323, Level 16, State 2, Line 1
The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-- Stopped SQL 2005 instabnce.
-- Copied the mdf and ldf file for this restoring state Database.
-- Dropped the DB in restoring state.
-- Tried to CREATE DATABASE DBNAme ON (Filename ='D:\File.mdf') FOR ATTACH_FORCE_REBUILD_LOG
-- This again failed with below error.
File activation failure. The physical file name "F:\Database\DBName\DBName_Log.LDF" may be incorrect.
New log file 'D:\MSSQL\SQL2005\Logs\DBNAme_log.LDF' was created.
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.
-- Here I noticed that the log file was getting created on F Drive and we did not had Drive: F on the box.
-- Mapped another drive with letter F: and created the folder structure, but same error.
-- Tried EXEC sp_attach_single_file_db but failed with
File activation failure. The physical file name "F:\Database\DBName\DBName_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DBNameDBName'. CREATE DATABASE is aborted.
-- Now tried the sp_attach_db command but failed again.
EXEC sp_attach_db @dbname = N'AdventureWorks',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf';
Msg 1824, Level 16, State 1, Line 1
Database cannot be restored.
-- Copied the original Data and Log files.
-- And tried attaching the files here.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DBName'. CREATE DATABASE is aborted.
Msg 9004, Level 21, State 1, Line 1
An error occurred while processing the log for database 'DBName'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
RESOLUTION
==========
-- Finally resorted back to Restoring the database from SQL 7.0 backup taken with CONTINUE_AFTER_ERROR paramter.
-- Then ran sp_reset status follwoed by restore with recovery this got the DB in accessible mode and was able to read the table.
-- Ran CHECKDB and this showed inconsistency on user Table.
-- Ran repair and this fixed the corruption, however there was some data loss which could not be recovered.