Before few month box of SQL has been crashed. We have almost database back with some tools and running application smoothly on it. But when i try to take backup of database at that time it gives error as below,
The log scan number (3533468:412:0) passed to log scan in database '****' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Microsoft SQL Server, Error: 9003)
Would anyone help me to out of this?
I have even try to copy database but i am failed.
Thanks in Advance.
Best Answer
First, why did you use these tools instead of simply restoring from a backup? My guess is, you didn't have a backup, or you didn't have one that was recent enough to be usable. Okay, we'll deal with that later.
In the meantime, if I were in your shoes, I would
update my resume and sprinkle fairy dust all over itcreate a new, empty database, and start moving all of your tables, objects, users and code over to the new database (much of that can be done with wizards built right into Management Studio, or with 3rd party tools like Red Gate's SQL Compare / SQL Data Compare). Once that's done, you can drop the Frankenstein database you somehow managed to get running (not sure whether "some tools" deserve credit or not - I don't think they did you any big favors at all). Once that's dropped you can rename the new database, and everything should be back to normal.While it is possible that you may fix whatever problem your current database has with something like
DBCC CHECKDB ... WITH REPAIR_ALLOW_DATA_LOSS, that error message you're getting sounds a lot more heinous than a few bad pages. I would get your data out of those files as quickly as possible.Next, and I mean immediately next, establish a sensible backup and recovery strategy. This doesn't mean just taking backups, but taking backups and regularly testing that you can restore them. This question isn't about corruption or recovering from a crashed database, but the answers there certainly detail the ins and outs of the different recovery models. Typically, taking a nightly or weekly full backup is not enough - you want differential or log backups in between to minimize the potential for data loss in the event of a server crash. And just taking backups, as I've said, isn't enough - you need to validate that the backups you are taking can be restored on different hardware - you know, should your current hardware completely melt down.
After that, contact the vendor of these "tools" and ask for your money back. This is not the way to recover data, and they have obviously done some irrecoverable things to your database that require even more manual work on your part. It's a shame you didn't ask a question about what to do when the crash originally happened, because surely folks here would have been able to guide you on more reliable approaches to recovery. I find that typically people who use these tools that promise complete recovery of a suspect or corrupt database, without having any idea what you might have done to it, only try them because of a misguided and/or panicked Google search. These vendors are great at SEO, but not so great at actually delivering on their far-fetched promises.