Database Corruption While Restoring With Attached Snapshots

Database snapshots may be defined as the read-only view of the database. They are useful when the database recovery is demanded as you can apply the pages from the snapshots to the source database. These provide the static view of the source database and can be created at one point of time in which the uncommitted transactions are not included.

The sparse files are used by these snapshots to store the pages of the database. These pages must be on the same server instance as the database. But if the source database becomes unavailable for any reason then the database snapshots also become unavailable. They provide the wonderful option to revert back the SQL database to the one before some transactions.

However there is a problem with these snapshots which ultimately may lead to SQL database corruption. If you have created a snapshot of your SQL database and then have dropped a table or any object in it, then this transaction will not be committed in the snapshot. After this if you backup your database and then restore from it then reverting to the snapshot may result into database corruption. An error message may appear when you try to run any query on the database:

"Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1664; actual 0:0). It occurred during a read of page (1:1664) in database ID 8 at offset 0x00000000d00000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. Additional messages in the SQL Server error log or system event 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."

This error message shows database corruption. So this is recommended that you should delete all the snapshots before restore the database. However in few caues when you try to restore the database with snapshots attached the following error message may appear:

"Msg 5094, Level 16, State 2, Line 2

The operation cannot be performed on a database with database snapshots or active DBCC replicas.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally."

However renaming the database before restoring can solve this error message issue but the database corruption still persists and an urgent need of SQL database repair may be there.

The SQL database serve information resource for many organizations and SQL recovery in this case is very important. The SQL databases are used to store critical information like company's clients details, the deals done and the supplier's location and lots more. So in this condition losing information can be a devastating experience and SQL database repair is mandatory which should be safe and perfect.

Stellar Phoenix SQL Database Recovery is the most advanced help for the same. This SQL database repair tool is able to repair all the database objects like stored procedures, triggers, user defined data types and all the database constraints.

This SQL recovery software is the complete solution, applicable for all the instances of database corruption. The user interface is quite interactive and demands no prior technical knowledge. You can have a check over its usability through the demo version of the software, available from the company's website.

Article submitted Tuesday, July 01, 2008
This article has been viewed 1 times.