The scary story of a corrupt database ... and the review of the Stellar Repair for MS SQL!
It is morning and you are just arriving at the office. You still have to take your invigorating coffee.
Suddenly the phone rings: your most important customer is blocked! He is no longer able to enter the management system, production is stopped!
Moments of silence that seem interminable!
Suddenly you feel less craving for coffee and more thirsty and hot!
With a software whose purpose is to recover SQL Server type databases.
Today I want to show you one in particular wich is called Stellar repair for MS SQL and is produced by the english company Stellar specialized in data recovery software, file repair and services.
We will see the installation but above all we will put it to the test.
I have a collection of corrupt databases..
Download and Installation
It's size is small, only 6 megabyte:
With a size of only 6 megabyte
Once downloaded just do a double-click on the exe file..
Now click on the next button:
Accept the license agreement and press the next button again:
Choose where to install the program and press the next button
Choose where setup place the program's shortcuts and press next
Do you want to add additional shortcuts? ...press next button:
the setup is completed just press the Install button.
After just some seconds the program is ready to run
To register the software simply press the Activation button, enter your licence key and press the Activate button.
This message will appear:
Recover a database
When the program starts it is automatically asked to select the database to recover.
Here it is necessary to indicate the path that points to a file with the extension mdf.
It is possible to choose to recover deleted records as well
Since we have a real corrupted database, for this first test we will try to recover it.
Let's indicate the path where our mdf file is located and press the Repair button
We have two modes of recovery: standard or Advanced.
We will start with the standard mode.
A first step of scanning of the structures to be repaired will stard while a progressbar shows the progress of the work..
What i like is that the result of this scanning phase is saved so i can re-use it if i need.
I like also very much that data are presented to the user in a ""SSMS style"
For each table i can see columns,foreign keys, unique keys, indexes, statistics..etc.
At a dababase level i can see views and all the programmability part such as Stored Procedure, Function, Trigger, etc..
The summary written in the log report is also very useful. Comparing the number of SP, FK in both databases (and write this information to our client) is the first of the checks!
And now go!
The last step is to save the recovered information.
We can choose whether to save them into a new database, into an existing database or whether to save them in another format (csv, xls or html).
I choose the first possibility I create a new database which later I will restore.
Now press the Next Button.
The information relating to the server to connect to and the relevant login remains to be entered
Press the Next button and choose the "save mode". I choose the Standard saving.
One table at a time will be recupered and when the process has finished exiting this message:
The log shows the number of rows saved for each table.
And this is the mdf file created at the end of the process:
I have attached mdf and ldf without any problem (while i was not able to attach the corrupted mdf)
I must say that in this case I see correctly recovered, primary key and unique key but no foreign key! Other objets types are ok!
Deep tests
Here we are at the first in-depth test called Constistency error on a user table.
For the first deep test I use a corrupted database voluntarily by me. Inside we have only one user table (with 100 rows) where I went to modify the data with the DBCC WRITEPAGE undocumented command thus generating a checksum error.
By executing the SELECT statement below:
I get this message:
SELECT * FROM corrupted_db_data..corrupted_usertable
Msg 824, Level 24, State 2, Line 110
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xf673c379; actual: 0xf673dc79). It occurred during a read of page (1:267) in database ID 6 at offset 0x00000000216000 in file 'C:\scambio\corrupted_db_data.mdf'. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Completion time: 2021-12-19T21:49:22.8646037+01:00
Checking the database with the DBCC CHECKDB command i found:
Msg 8939, Level 16, State 98, Line 112
Errore di tabella: ID di oggetto 613577224, ID di indice 1, ID di partizione 72057594043236352, ID di unità di allocazione 72057594049724416 (tipo In-row data), pagina (1:267). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) non riuscito. Valori: 133129 e -4.
Msg 8928, Level 16, State 1, Line 112
ID di oggetto 613577224, ID di indice 1, ID di partizione 72057594043236352, ID di unità di allocazione 72057594049724416 (tipo In-row data): non è possibile elaborare la pagina (1:267). Per altre informazioni, vedere gli altri errori.
Msg 8976, Level 16, State 1, Line 112
Errore di tabella: ID di oggetto 613577224, ID di indice 1, ID di partizione 72057594043236352, ID di unità di allocazione 72057594049724416 (tipo In-row data). Impossibile rilevare la pagina (1:267) durante l'analisi, anche se la pagina padre (1:265) e quella precedente (1:266) vi fanno riferimento. Controllare eventuali errori precedenti.
Msg 8978, Level 16, State 1, Line 112
Errore di tabella: ID di oggetto 613577224, ID di indice 1, ID di partizione 72057594043236352, ID di unità di allocazione 72057594049724416 (tipo In-row data). Nella pagina (1:268) manca un riferimento dalla pagina precedente (1:267). Possibile problema nel collegamento a catena.
DBCC results for 'corrupted_usertable'.
There are 68 rows in 3 pages for "corrupted_usertable" object.
CHECKDB found 0 allocation errors and 4 consistency error in the 'corrupted_usertable' table (Object ID 613577224).
Let's now execute Stellar Repair with the advanced option.
The software recognizes that there are 100 rows in the table:
No error messages are displayed when saving the data to a new database.
Stellar repair did a great job in this case.
We have the entire table readable (100 rows) and the field ID of the single row that i have corrupted took the value that I had put.
The second in-depth test we try to repair a database where there is a problem on the IAM page.
This is the result of a DBCC CHECKDB command:
DBCC results for 'corrupted_db_data'.
Msg 8928, Level 16, State 6, Line 297
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:79) could not be processed. See other errors for details.
Msg 8905, Level 16, State 1, Line 297
Extent (1:264) in database ID 6 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8905, Level 16, State 1, Line 297
Extent (1:352) in database ID 6 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
CHECKDB found 3 allocation errors and 0 consistency errors not associated with any single object.
DBCC results for 'sys.sysrscols'.
There are 1243 rows in 15 pages for object "sys.sysrscols".
[...]
There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
DBCC results for 'sys.wpr_bucket_table'.
There are 0 rows in 0 pages for object "sys.wpr_bucket_table".
Msg 8939, Level 16, State 98, Line 297
Table error: Object ID 613577224, index ID 1, partition ID 72057594043236352, alloc unit ID 72057594049724416 (type In-row data), page (1:79). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8906, Level 16, State 1, Line 297
Page (1:79) in database ID 6 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 2575, Level 16, State 1, Line 297
The Index Allocation Map (IAM) page (1:79) is pointed to by the next pointer of IAM page (0:0) in object ID 613577224, index ID 1, partition ID 72057594043236352, alloc unit ID 72057594049724416 (type In-row data), but it was not detected in the scan.
Msg 8939, Level 16, State 98, Line 297
Table error: Object ID 613577224, index ID 1, partition ID 72057594043236352, alloc unit ID 72057594049724416 (type In-row data), page (1:79). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 7965, Level 16, State 2, Line 297
Table error: Could not check object ID 613577224, index ID 1, partition ID 72057594043236352, alloc unit ID 72057594049724416 (type In-row data) due to invalid allocation (IAM) page(s).
DBCC results for 'corrupted_usertable'.
There are 0 rows in 0 pages for object "corrupted_usertable".
CHECKDB found 3 allocation errors and 2 consistency errors in table 'corrupted_usertable' (object ID 613577224).
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKDB found 6 allocation errors and 2 consistency errors in database 'corrupted_db_data'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (corrupted_db_data).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Completion time: 2021-12-20T23:02:57.3079321+01:00
After running the Stellar repair i was able to attach the database.
The DBCC CHECKDB command shows no error! very good!
Conclusions
Well guys, that's all for today.
In the next post we will talk about ....adaptive join doing some theory and some practical examples, so Stay Tuned!
Luca
Next post:
Previous post: SQL Server, why does the DELETE statement tend to be slow?