restore database from transaction logs
-
Guys I have a question about restoring sql server databases. Our HR manger had an accident with one of the hr databases we went to restore the database and it was a no go. First off the database server that used to house the hr databases was moved to a whole new server when this was done the backup jobs somehow got over looked and was not recreated. The database was moved to the new server by being detached the mdf and ldf files were all move. The database has not been backed up in over a month but there is a transaction log. So we restore the month old database than apply the trans log and this
error was kicked out
Server: Msg 4305, Level 16, State 1, Line 6
The log in this backup set begins at LSN 12033000000019200001, which is too late to apply to the database. An earlier log backup that includes LSN 12013000000023300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE LOG is terminating abnormally.
There seems to be some log files missing I think when the server was migrated they did not grab the old log backups.
So my question is can a sql server database be recovered without having all the logs?
-
Unfortunatley without the previous log or log files you will not be able to recover this particular log file.
-
The old server was sent back to the leasing company a month ago. I will have to contact them and try to get it back hoping they did not sell it or wipe the drives yet. One of our oracle admins told me that I could do this if it was oracle. Even if I could get some of the data back that would be better than nothing from what you are saying we are screwed. I've done the backup schedule myself this time I have it backing up 2 times a day and the logs every hour. After this blows over I'm going to see if I can get approval to redo security a user should not have the abiltiy to drop tables and such anyway. Also in regards to the logs I thought when you did a log backup it was the entire log for the database and is not dependent on an old log.
-
Nope...a log backup is all the transactions since the last backup of any kind. If you do a log backup after a differential - the log has the transactions that happened since the diff was made. If you do the log backup after another log backup - the 2nd log backup has the transactions that happened since the 1st log backup. Log backups are dependent upon the most recent FULL backup.
-SQLBill
-
We ended up using sql log rescue. We basically opened up the trans log that we already had rolled it back after that the program generated a sql script to apply to the current database the script was 100megs. I tried to run it from the query analyzer gui and got a system memory error even though the server has 4 gigs in it. So I ran the query from the command line with OSQl. The query has been running for about 20 minutes so I'm hoping this works. All I can say is there should be some sort of tool built into sql server that can do this. I know we should have had the logs but thats not always the case.
-
That is certainly a good option. Perhaps someone here should have suggested it... and perhaps you actually found other discussion on the same subject here; because it has been discussed a lot recently.
Be sure to stop your script before you get to the failure that caused the problem in the first place... for instance, if your HR manager deleted a table, stop your script before it deletes it again.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
Let me ramble on a bit more... you definitely did a good job in getting to where you are. Tell us how you found out about the tool. How much does it cost? Is there a trial version that works with real data?
Give us details. Sometimes it's easier to say "you're screwed" than to provide all the alternatives. Let's leave a thread here to remind us how to get out of this problem.
I am not sure you could have done it with oracle either... not without a log reader, and there is one that comes with (or used to come with) Oracle; but my memory is that it was so hard to use that you never used it. Out of the box, a restore will stop with the first out of sequence log file. <period>
Another thing that a lot of people don't understand is that unlogged transactions will invalidate your backups. On most systems, and I believe SQL Server (at least, 2005) has made an attempt to correct this with "minimally logged" actions... if a truncate command is executed - that will become the end of your restore/recovery. Doesn't matter that the data truncated wasn't (isn't) required... it just plan requires a full backup after it is issued.
yes, "secutiry" being what it is... usually not "foolproof", there are usually people who don't know this, or care, who can execute this command.
Anyway, I hope your log restore is going well. Keep us posted.
David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
We used log rescuer from these guys
Its around 194.00 the trial version works but it will not let you save the sql script that it genertates. We used filemon from sysinternals to get around that basically filemon will let you see what all files are associated with a exe we found that it was putting the sql script in a tmp file we than used textpad to open the tmp file and thats how we got the script. We were not going to pay money up front because we did not know if it would work after it worked we actually bought the product for use. I know we could not have done this using conventional tools the ms support people said we were dead in the water and I started thinking theres got to be a way to read your transaction log. So after all that we had to edit the script a bit a few syntax errors we than ran it through the query analyzer it took about 2 hr 30 to run it was about a 90meg script file.
Can you explain this a little more I'm new to databases
"Another thing that a lot of people don't understand is that unlogged transactions will invalidate your backups. On most systems, and I believe SQL Server (at least, 2005) has made an attempt to correct this with "minimally logged" actions... if a truncate command is executed - that will become the end of your restore/recovery. Doesn't matter that the data truncated wasn't (isn't) required... it just plan requires a full backup after it is issued"
-
if anyone executes an unlogged transaction (for instance, truncate) the full backup and subsequent log files can only be restored/recovered up to that command. After executing an unlogged transaction you should take a full backup.
Every time this topic is discussed there is a thread of exceptions and denials... and MS has attempted to change this with "minimally logged transactions"... although I am not sure how effective that is... Let someone who knows comment
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply