0

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.

  • 5
    Could you describe what you mean by "with some tools"? What tools? Exactly what did you do? Let me guess, tools that promised 100% recovery for corrupt databases? If so, you're living proof of how full of crap those folks are. Also, why are you worried about taking a backup now? Shouldn't you have been worried about that before the server crashed? Whatever you do, don't restart the server, and don't detach the database. Create a new database, and salvage as much data as you possibly can from this old Frankenstein you somehow managed to get running again. This is a textbook time bomb. – Aaron Bertrand May 28 '14 at 18:07
  • Would you care to tell us what tools you tried to use? Also, "before few month" does that mean you have a database running without any form of backup, on production, for several months? I suggest after this disaster is dealt with you might like to read brentozar.com/needs . – Reaces May 28 '14 at 21:12
  • I have recently joined with this company. And dont have such backups and restores plan before. I have just make it prepared now with full and incremental with TLog backup. Thanks a lot for this advise. – jskalasariya May 29 '14 at 5:42
  • Use Maintenance plan to schedule the backup plan – user39195 May 29 '14 at 12:15
7

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 it create 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.

  • Common Aaron, tell us how you really feel. – mrdenny May 28 '14 at 20:39
  • @mrdenny you mean with more swearing? :-) – Aaron Bertrand May 28 '14 at 20:42
  • Yes please. That always is entertaining. Especially when I've been up since 3am. – mrdenny May 28 '14 at 20:43
  • @Aaron Thanks for your valuable inputs. I am just preparing to copy database to new database as you just mentioned. I have just join this company before a month only. And We didn't have any backup and restoration strategies before. – jskalasariya May 29 '14 at 5:45
0

ALTER DATABASE dssp SET EMERGENCY ALTER DATABASE dssp REBUILD LOG ON (NAME=dssp_log, FILENAME='E:\MSSQL\DATA\dssp_log1.LDF') DBCC CHECKDB (dssp) ALTER DATABASE dssp SET MULTI_USER

Works Fine...Thanks all for your valuable inputs...:)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.