Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to Create a Corrupt SQL Server Database Expand / Collapse
Author
Message
Posted Monday, May 7, 2012 1:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1295808
Posted Wednesday, June 27, 2012 6:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 2,445, Visits: 1,027
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


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1321831
Posted Wednesday, June 27, 2012 8:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,619, Visits: 14,185
hi thanks for your comments, hope you found it useful
Regards Pez


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1321911
Posted Wednesday, August 22, 2012 6:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:32 AM
Points: 170, Visits: 740
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
Post #1348341
Posted Friday, August 8, 2014 1:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:53 AM
Points: 97, Visits: 67
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.
Post #1601051
Posted Friday, August 8, 2014 1:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1601056
Posted Friday, August 8, 2014 3:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:00 AM
Points: 542, Visits: 2,136
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.
Post #1601083
Posted Friday, August 8, 2014 6:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 10:16 AM
Points: 19, Visits: 60
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
Post #1601140
Posted Friday, August 8, 2014 7:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:55 AM
Points: 346, Visits: 310
Great article. It really highlights the concept of knowing how to break something so you can better learn how it can be fixed.
Post #1601153
Posted Friday, August 8, 2014 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1601222
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse