SQL Server Performance Forum – Threads Archive
Simulating Errors
How could I create an allocation or consistency error (or otherwise corrupt a db) in a database to test a backup and restore system? Joe JankaTo test data recovery, you actually don’t need to ‘create allocation/consistency error’. You can: 1. Enter bad/nonsense data into a table and decide to restore a good copy of the database/transaction log.
2. Delete some data and then restore the database/filegroup
3. Stop SQL Server and delete the mdf files associated with the database and restore the whole database.
4. Format the hard disk (wow!) and….. Allocation/consistency errors are pretty difficult. You may need to destroy more than just the database in question to achieve that (e.g. your hardware,power supply etc). I just hope that you are not planning on doing this on a VERY crucial database. If that is the case, make sure that you also have a good backup of your resume somewhere. HTH. Nathan H. Omukwenyi
I have one suggesion to check the consistency of the backed-up data, You can dettach the database and place the files in different locations, then try to restore the data from the backup devices with the same or different database name, so that you can compare the data. But when you do the file movement from any of the mdf,ldf or ndf and when you start your service, the same database will be shown in "suspect". You can not check any error, only the error indication is "file allocation error" which is understud. -Johnson
no no no, a test database on a test server. no production is going to be able to get a sniff of all of this!!!!!!!!!!!!!!! Joe Janka
allocation or consistency errors are what i am trying to simulate, getting it to show in "suspect" won’t help. we want the db to look for inconsitencies in the data before it backs it up, if it finds an inconsistency, it does not run the backup of the database we want to test the system by making this kind of error and forcing the system to show us it will work Joe Janka
ok.. I think I can make it out the question, but I realy don’t have any idea how to make an data inconsistency. Theeoraticaly speaking, currupting the indexes or data.. or currupting any pages, your mission will work.. but I am clueless how to make it forcefully, it happened to me accidently. I am also expecting for any such tips and tricks -Johnson
well, the binary editor idea is looking like a wash, i am changing things that I would assume do nothing, then changing things I assume would do something, the deleting some things that I would assume would bring up errors. but for some reason, it does not lead to the desired effect, which is to not get "CHECKDB found 0 allocation errors and 0 consistency errors in database…" but instead a number in one of the "0"’s place Joe Janka
]]>