Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
 Posted - 2006-12-28 : 13:15:37
|
| The following list of actions leads to a corrupt database on SQL Server 2005.Create a database snapshotDrop a table in the databaseBackup the databaseRestore from backupRevert to the snapshotI'm not entirely surprised that it results in a corruption, what is surprising is that I can revert from a snapshot after restoring. There needs to be some kind of check to prevent reverting to a snapshot in a case like this. Until SQL Server prevents you from doing it, I'd recommend a best practice is to delete all snapshots before you restore a database so that you cannot do this by accident. |
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2006-12-29 : 00:48:25
|
quote:
what is surprising is that I can revert from a snapshot after restoring.
yes its surprising, thnx for sharing this  its me monty |
 |
|
|
kfarlee
Microsoft SQL Server Product Team
9 Posts |
Posted - 2007-01-02 : 13:58:52
|
| What is even more surprising to me is that you don't get the same error message I do when attempting to restore over a database with a DB snapshot:Msg 5094, Level 16, State 2, Line 2The operation cannot be performed on a database with database snapshots or active DBCC replicas.Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.That's the message I get when I attempt to restore over a database with snapshots attached.Can you let us know what build/SP of SQL 2005 you're running, and what commands/tools you're using to do the restore?There may be a case that we're letting slip through, and I'd like to get that fixed.Kevin FarleeSQL Server Storage Engine PM |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-03 : 15:09:04
|
| Hi Kevin,Sorry, I realized I left out an important step in the series of things I did when I saw this.1. Create a snapshot.2. Drop a table in the database.3. Backup the database.4. Simulate a failure of the database - I stopped SQL Server, renamed the database file, then restarted SQL Server.5. Restore the database - now you will be able to restore and you will not get the error message you mentioned. The snapshot is still there.6. After restoring, the dropped table will not be in the database as you would expect. If you try to query it in the snapshot, you get this error:Msg 601, Level 12, State 3, Line 1Could not continue scan with NOLOCK due to data movement.7. Now revert to the snapshot - this is the that I was surprised to be able to do.8. After reverting the table is now corrupt - you get the following error if you query it in the database:Msg 824, Level 24, State 2, Line 1SQL 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.and you still get this error if you try to query the table in the snapshot:Msg 601, Level 12, State 3, Line 1Could not continue scan with NOLOCK due to data movement. |
|
|
|
|
|
|
|
|
Database Corrupted by Reverting to a Snapshot