April 14, 2010 at 5:13 pm
I manage a SQL Server 2000 database at my office (I'm not a DBA...just the person most likely to be able to do the job). Yesterday I ran one of our monthly stored procedures to update a production file with March's earnings data. After the job ran, I discovered an error in the stored procedure that wrote inaccurate data to the file. I had been taking full weekly backups of the database with daily transaction log backups, so tried to restore to that morning's data. This is where the problems began.
I didn't realize that the weekly full backup had failed starting in early March. So, when I tried to restore the data through Enterprise Manager, the restore failed and left the database in a suspect state. The weekly backups overwrote the old backup, so in early March only about 60% of the data was saved to the full backup file. This is the most recent full backup which is obviously incomplete. The daily backups had continued without any errors. I think when the restore process started with the incomplete backup, this is what caused the restore to fail.
I took copies of the .mdf and .ldf files (which I don't think were changed by the failed restore) and tried several other fixes to restore the database without success. So, now I've decided to restore the .mdf and .ldf files and delete the inaccurate production data and recreate it from scratch. I stopped the instance, overwrote the .mdf and .ldf files with the original versions and restarted the instance. For some reason, I am not seeing any data more recent than April 2009. The .ldf file is 23.4GB - significantly larger than the .mdf file at 1.1GB. This makes me to think that the .ldf data is not being applied to the database. These are the same file sizes as they were when the inaccurate data was written to the database yesterday, so I believe they represent the most recent data in the database. I'm not sure if this is correct or even how to check...let alone how to go about fixing the problem.
I've searched through the forums and found plenty of suggestions about restoring/copying .mdf and .ldf files, but haven't found a solution to my specific problem. Any suggestions would be appreciated. I've been trying to fix this for most of the last 24 hours without any progress.
Thanks
April 14, 2010 at 8:43 pm
How did you copy the mdf/ldf files in the first place? Did you shut down SQL Server first - then copy the files? If so, then attaching those files back should have returned the system to the state it was in when you shut down SQL Server.
Your other option is to find the most recent good backup, restore that backup file with norecovery option and then start restoring your transaction log backups up to the point in time when you ran the procedure. This is dependent upon whether or not you have an un-broken log chain from that good backup. If you have any processes that break the log chain, you are not going to be able to restore past that point.
Hopefully, you realize that your current backup plan is not adequate. I would recommend at least daily backups and hourly transaction log backups. Just to let you know, I have several databases that more than 200GB that I backup daily as well as several 800GB+ databases which also get backed up daily.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 16, 2010 at 6:46 am
Hey there,
The ldf is likely to be much larger than the database mdf itself, because probably no log backups are being taken...it will have all transactions.
Normally the best practise before running a large update query would be to take a manual db backup, but as this wasn't done, do you know when the last backup actually worked? Were any .bak files written to tape or to file backups?
This is probably your only realistic restore point at this stage, and you might be able to look at the logs to see what other more recent information can be restored...
April 17, 2010 at 8:11 pm
Just to clarify, you took copies of the .mdf/.ldf files before you started the bad restore, or after? If it was after, then you are most likely in the same state that you are in when you tried to perform the restore. If that is the case, then I think Jeffrey's option is the best one you have if, like he said, you have all of the log backups from the time of the last good full backup to the time of the most recent log backup.
Curious, is your system backed up in any other ways as well? Such as OS backups? If that is the case then you may be able to check with the sysadmin who runs the backups and see if the database files are copied that way as well.
Also, is there any chance your databases are hosted on a SAN? If so, you may be getting volume/LUN snapshots that you can take advantage of.
Joie Andrew
"Since 1982"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply