Help! Restoring Trans Logs

Question

Help! Restoring Trans Logs

tymberwyld

SSCertifiable

Points: 7810
More actions
July 24, 2004 at 10:31 am

#102124

I have restored a DB from another Server onto my local Machine. I want to start restoring Differential Trans. Logs that I have for it. However, in the EM (Enterprise Manager) there are no Logs showing for it. If I try to Specify the Device as a File name (the TRN file) and choose "Transaction Log" to try to Restore from it...I get this Error:
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
RESTORE LOG is terminating abnormally.
How can I restore these Transaction Logs???

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

Given that you're using EM, when you first restore the full backup, look in the Options page, and in the Recovery completion state values. Select either the 2nd or 3rd option. Thereafter, you will be able to restore the transaction logs, but you must get them in the right sequence. Once you have completed restoring all the logs, select the 1st option.

Using the 'From device' option is correct. Since the backups did not originate from your machine, you do not see them listed by default.

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.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 2

Well, I think I am doomed. I did what you said, which worked...to a point. However, my T-Logs are not in order. I am missing one day's worth because the IS Dept. doesn't have them. I have a DB from 7-13 and I want to restore a Log from 7-15. I really don't care about the Transactions from 7-14. Is it possible to restore this?

If not, does anyone know of a 3rd party tool that could possibly convert the Log's Transactions into Statements that could be executed?

tymberwyld SSCertifiable Points: 7810 More actions · Answer 3

Well, I've discovered it's impossible to restore from T-Logs if you don't have every single one of them from begining to end. So I ask...what good are they??

Anyway, I downloaded ApexSQL Log and I'm using it to read the T-Logs to Export "Re-Do" Scripts.

Thanks for the help!

Ray Mond SSCertifiable Points: 7159 More actions · Answer 4

>> So I ask...what good are they??

The primary advantage is that each trx log is usually smaller than a full or differential backup and completes faster, so that you can perform them more frequently (which you should if your database is write intensive), and allows you to restore to a specific point in time.

Re the missing log file for 7-14, I would think that it's more a problem with your IS dept than anything else. Trx logs should be kept just as securely as a full/differential backup.

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.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 5

I guess it was more of a hypothetical question. I know they are good to have. Unfortunately, I cannot control the client's IS Dept. However, I just wish SQL Server would be able to use T-Logs more effeciently. I mean, why can't they give you options to restore even though you don't have every transaction? Or at least give the ability to convert the T-Log into some "readable" Scripts to run?? Why should a 3rd party have to develop something that should come with SQL Server?

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 6

errr, you do know that you're not going to be able to recreate the data from the Log Explorer, right?????? When items are written to a transaction log backups, they are no longer stored in the transaction logs. That's the reason you need to have ALL the transaction log backups. It makes a whole lot of sense if you think about it.

It would be stupid to even think that they should let you skip around on what transaction log backups you want to restore. How are they going to figure out what you did between 1 and 5 for example without the intermediate logs...guess??? Without ALL the log files, there can be no relational integrity at all, which is the whole point of having a relational database. Log Explorer might let you create a brute force script to do whatever you're trying to do. All you're going to end up with when it's done though is junk.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 7

Well, there's comes a point and time when you'll be working all weekend trying to fix a client's data...and because it's so screwed up you don't really care anymore about integrity because if it's not fixed on Monday, you're screwed. So...any data you can get will be better than absolutely nothing at all.

I've been using ApexSQL Log's Evaluation and it's awesome! I've been able to create "Redo" Scripts to at least recreate the Transactions from the points in time that I know are good (ex. 8AM - 5PM). Sure I am missing one day, but I think that missing one day versus 8 days worth of data is a lot better .