Analyzing a T-Log, problems

Question

Analyzing a T-Log, problems

dde

SSC Eights!

Points: 891
More actions
September 29, 2007 at 5:07 pm

#118732

I am using SQL Log Rescue to try and see if I can revert a few UPDATES. I created a DB backup and restored in another computer.
What may I be doing wrong? I get a message saying there is nothing in the transaction log file.

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

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

in order to use SQL Log Rescue, the database Recovery Model Option had to be set to "Full" and not "Simple" prior to the UPDATE event occurring. Check the properties of the database and confirm it's set to "Full"

I've got a feeling that's the issue, and you can use Log Rescue for any events that occur AFTER it has been switched to Full Recovery.

In this case, you probably won't be able to reverse the UPDATES in question if the db recovery mode was set to Simple. If you have a previous backup, you could restore it as a different database, and compare the old version agaisnt the ncurrent for the differences.

oops since your testing, if you update the data again, if you reopent he SQL Log Rescue Project, it will immediately show the updates you can roll back

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dde SSC Eights! Points: 891 More actions · Answer 2

It is set to FULL. After restoring backup locally I got two files, one MDF 2.2GB and one LDF 3.3GB big.

I ran one popular script I found here from Rodrigo Acosta

SP to show information about log in databases

[/url]

For the remote server I have

Log information

---------------

Log Size in Mb: 3243.992

Log Used in Mb: 1366.597Log Used in Percent: 42.127

Log Free in Mb: 1877.395Log Free in Percent: 57.873

However, after I restore locally I get

Log information

---------------

Log Size in Mb: 3243.992

Log Used in Mb: 54.759Log Used in Percent: 1.688

Log Free in Mb: 3189.233Log Free in Percent: 98.312

So, what is happening here? I did create backup using Enterprise Manager and I believe I selected all appropriate settings.

Lowell SSC Guru Points: 323506 More actions · Answer 3

the server is local or remote from where you installed SQL Log Rescue? i know you have to install the extended stored procedures on each instance you want to use Log Rescue on....

my test server is local, but I also installed them on our team development server as well. the local installation the program was able to do automatically with no problem, but for the remote it was slightly different.

In that case I had to actually copy the files to the binn folder on the server and then run the SQL provided by the program to register them...the code for that is given to you in the Log Rescue program.

after i did that, i added a table, and updated a few rows in a different table, went to the project againa nd it found my changes...you are not seeing the same thing?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dde SSC Eights! Points: 891 More actions · Answer 4

I tried on both local and remote. I even tried from local, via VPN to remote and added the required xp.

I am afraid the cause is this instruccion being run at a certain interval

BACKUP LOG my_db WITH TRUNCATE_ONLY; DBCC SHRINKFILE(my_db_log, 2000);

But I do not know, perhaps opening live log will show transactions, Used space is high in live log, but not when I restore somewhere else.

What do you think?

Lowell SSC Guru Points: 323506 More actions · Answer 5

well WITH TRUNCATE_ONLY would truncate the log, but that's only whent he backup occurs.

if you restored it locally, made sure it was set to Full, and then updated a row, that change is in the transaction log until the backup occurs....that's what log rescue is reading.

the LDF might be reserving 3.3 gig, but it's most likely empty and awaiting data transactions, that's normal.

It sounds like you are doing everything right.

try starting a new project again on your local server. first time, it might be reporting nothing int he log.try updating some rows in a table, and then re-open the project...it does a snapshot, it doesn't watch for changes the Lumigent Log Explorer does. When you reload the project, you should see the updates on the list so you can roll them back selectively.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dde SSC Eights! Points: 891 More actions · Answer 6

try starting a new project again on your local server. first time, it might be reporting nothing int he log.try updating some rows in a table, and then re-open the project...it does a snapshot, it doesn't watch for changes the Lumigent Log Explorer does. When you reload the project, you should see the updates on the list so you can roll them back selectively.

I tried this, it went bad. All references to fields, users, etc now appear as 'unknown'