SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Create a Corrupt SQL Server Database


How to Create a Corrupt SQL Server Database

Author
Message
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26591 Visits: 17335
Thanks for your feedback, please don't forget to rate the article to.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
quackhandle1975
quackhandle1975
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3488 Visits: 1242
Five stars for me as I find this type of article almost a "reverse engineering" concept, sometimes this can help with the understanding.

Good stuff.

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26591 Visits: 17335
hi thanks for your comments, hope you found it useful
Regards Pez

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Database admin(DBA)
Database admin(DBA)
Right there with Babe
Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)

Group: General Forum Members
Points: 734 Visits: 1464
mY database will be corrupted [font="Arial"][/font].so how to recovery the corrupt database??????( i have little bit confusing.because of iam using below script reocvery the database :

DBCC CHECKDB ('Corrupt2K8') WITH NO_INFOMSGS, ALL_ERRORMSGS
exec sp_resetstatus 'Corrupt2K8'
alter database Corrupt2K8 set emergency
dbcc checkdb('Corrupt2K8')
alter database Corrupt2K8 set single_user with rollback immediate
dbcc checkdb('Corrupt2K8', REPAIR_ALLOW_DATA_LOSS)
alter database Corrupt2K8 set multi_user ))

NOTE: some points for consideration:

Then restore My database from a backup - but make sure to run DBCC CHECKDB on that very backup to ensure it's not corrupt itself.

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is the very last resort . Before that, you should restore a valid backup. Once you do use REPAIR_ALLOW_DATA_LOSS, don't expect your database to magically come back to life again - most likely, irreparable damage has occurred - especially in this case since it complains about system table corruption.

Thank you in advance

SQL server DBA
navneet.agarwal19
navneet.agarwal19
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 68
Hi, It worked well but as I execute command to bring the database online after modifying the .mdf file. SQL Server throws an error:

Msg 926, Level 14, State 1, Line 1
Database 'Corrupt2K8' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5125, Level 24, State 2, Line 1
File 'E:\SQL DATA\Corrupt2K8.mdf' appears to have been truncated by the operating system. Expected size is 524288 KB but actual size is 524280 KB.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'Corrupt2K8' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26591 Visits: 17335
This hasn't worked successfully for you, work through the guide again on a new database

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Michael Meierruth
Michael Meierruth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 2515
This corruption issue has always intrigued me.
I have always wondered whether anyone has done any investigation on an actually corrupt database to see where it has become corrupt and possibily also discover why.
Instead we all concentrate on just trying to recover from this situation - which I guess is good enough.

A little while back there was a discussion on corrupted backups and all the things that can and should be done to avoid it.
At the time I used some C code to randomly corrupt bytes here and there in a BAK file (did the backup after having done a compression).
It was amazing how much corruption you could apply and still be able to do a restore.
I too recently discovered this xvi32 tool and will now try it out on some MDF and BAK files.
roberto.regidor
roberto.regidor
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 95
Funny, I had understood that if a nonclustered index is corrupt, you don´t need the option REPAIR_ALLOW_DATA_LOSS, that is shown in the image
PHYData DBA
PHYData DBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1265 Visits: 537
Great article. It really highlights the concept of knowing how to break something so you can better learn how it can be fixed.
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26591 Visits: 17335
roberto.regidor (8/8/2014)
Funny, I had understood that if a nonclustered index is corrupt, you don´t need the option REPAIR_ALLOW_DATA_LOSS

That is correct


roberto.regidor (8/8/2014)
that is shown in the image

which image are you referring to?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search