July 1, 2010 at 9:43 am
I don't believe there is a solution to my problem, but I thought I would post it anyway. I was asked today to restore a SQL Server 7 (yeah I know) database to a point-in-time before a developer globally updated a table. I had not been involved with this database before and it is in a distant site.
Here's the situation. No backups of any kind for the past several years. A database that is about 2.0 GB in size and a transaction log that is about 600 MB. Since the error was just done, I know the data is still in the transcation log. The problem is that is has already been committed to the database.
If I understand correctly, if I do a database backup now, the transaction log data would be lost. If I now do a transaction log backup - I would not be able to apply it to an earlier database backup (because I have none).
Is there any trick I can perform to go back a time just before the bad update?
July 1, 2010 at 9:48 am
Without any type of bacup to apply the logs against, I don't think that you can. There are commercial tools available that can read log files but I have no experience of them so don't know if they would be of any use.
July 1, 2010 at 9:56 am
Sorry, no backup, no rollback. To undo a change, you need a backup from before the change that you're trying to undo.
If the DB has not been backed up, I'd be willing to bet that the DB is in simple recovery (or there's manual truncation of the log happening), in which case you either won't be able to back the log up, or the log backup would be useless because of the broken log chain. (7 and 2000 allowed log backups with a broken log chain, they were just useless for restore purposes)
You need this http://qa.sqlservercentral.com/Forums/Topic860953-61-1.aspx (if it existed...)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 5:53 pm
Gail's probably won the bet as regards to your database's recovery model. If so, don't do anything else that would overwrite what's already in the active transaction log. ApexSQL Log might be able to generate an undo script for you. This URL provides quite a thorough review of that application.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply