SQL server database corruption is a like a worst nightmare for every SQL server database users. For any organization; small to big, database integrity and availability is the most important part. Database administrator (DBA) is responsible for it, if you are a DBA of SQL server database then it’s your responsibility to manage the database and make a proper disaster plan to handle any kinds of database corruption scenarios. In this article, we will learn about a particular error message that appears during selection of data from a table.
Select data from table;
Select * from My_table_name
Go
But received below error message
“Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]'
(object ID 1326627769).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
([DB_Name]) ”
As the error message indicates, there is a corruption in the specified database. So don’t use the DBCC CHECKDB command with repair option first because you will loss amount of data from the database after executing it.
Restore from backup: It is suggested to restore the database from clean & updated full backup. It is the best solution to handle any kind of SQL database corruption.
Note: Before using your backup, you will need to verify it. ‘Restore verifyonly’ command checks the SQL server database backup and ensures that you are restoring your database from consistence backup. While restore only command ensures that backup is complete and can be used in all format.
If you don’t have updated backup then you can go with Repair command but you will need to put your database in single user mode first.
ALTER Database Database-Name SET SINGLE-USER
GO
Now you can run DBCC CHECKDB with repair_allow_data_loss on the database.
DBCC CHECKDB (Database-Name, Repair_Allow_Data_Loss)
GO
After running this command; you will get a message on your screen.
The error has been repaired.
Conclusion: It is very hard to recover data from corrupt database files without backup. Sometimes corrupt database file can be repaired, sometimes not. So always make a backup of database and regularly update it. Make this habit and add in your daily routine. Most of the times it has been observed that SQL database data lost during manual repairing process, to avoid this situation DBA’s can use SQL Recovery software to repair corrupt SQL database.
Note: ALL third party SQL database repair software are paid, there is no free tool available
Select data from table;
Select * from My_table_name
Go
But received below error message
“Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]'
(object ID 1326627769).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
([DB_Name]) ”
As the error message indicates, there is a corruption in the specified database. So don’t use the DBCC CHECKDB command with repair option first because you will loss amount of data from the database after executing it.
Restore from backup: It is suggested to restore the database from clean & updated full backup. It is the best solution to handle any kind of SQL database corruption.
Note: Before using your backup, you will need to verify it. ‘Restore verifyonly’ command checks the SQL server database backup and ensures that you are restoring your database from consistence backup. While restore only command ensures that backup is complete and can be used in all format.
If you don’t have updated backup then you can go with Repair command but you will need to put your database in single user mode first.
ALTER Database Database-Name SET SINGLE-USER
GO
Now you can run DBCC CHECKDB with repair_allow_data_loss on the database.
DBCC CHECKDB (Database-Name, Repair_Allow_Data_Loss)
GO
After running this command; you will get a message on your screen.
The error has been repaired.
Conclusion: It is very hard to recover data from corrupt database files without backup. Sometimes corrupt database file can be repaired, sometimes not. So always make a backup of database and regularly update it. Make this habit and add in your daily routine. Most of the times it has been observed that SQL database data lost during manual repairing process, to avoid this situation DBA’s can use SQL Recovery software to repair corrupt SQL database.
Note: ALL third party SQL database repair software are paid, there is no free tool available
No comments:
Post a Comment