Solved

SQL Server 2000 Transaction Logs TRN Files

Posted on 2013-05-13
9
736 Views
Last Modified: 2013-06-16
I have a two part question. 1) Is there any TRN log viewer that I can see what is in the log file. Most of what I have been able to find are actually apps to use the TRN files to restore back to a database. I just want to view what is in the TRN file, possible? 2) In SQL 2000 what is the exact procedure to use TRN files, I have some from 6,7,8,9,10,11.....to 6PM that I want to restore to a database from 5AM. Thanks.
0
Comment
Question by:Lionel MM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 2

Assisted Solution

by:Psihawk99
Psihawk99 earned 125 total points
ID: 39161386
I think I have the second part.  I am not familiar with an app that can look into the log files.  Though this free tool might work for you:
http://www.red-gate.com/products/dba/sql-log-rescue/
Red Gate has some good free tools and pay ones as well that make System Admin easier.


(for the DB restore)

RESTORE DATABASE NewDatabase

FROM DISK = 'D: \BackupFiles\TestDatabaseFullBackup.bak'

WITH

MOVE 'PreviousDatabase' TO 'D:\DataFiles \TestDatabase.mdf',

MOVE 'PreviousDatabase_log' TO 'D:\DataFiles \TestDatabase_Log.ldf',

NORECOVERY

Then to apply the logs (In this case there are only 3 logs to get to the point in time):

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\TestDatabase_TransactionLogBackup1.trn'

WITH NORECOVERY

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup2.trn'

WITH NORECOVERY

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup3.trn'

WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39163253
1) Not free, that are easily useable.  I think there are some 3rd party products, i.e. cost $s, that can do that.


2) To recover a db in SQL Server:

You must first restore a full database backup.

You may then, optionally, apply a differential backup.

Then you must apply *ALL* log files, **IN THE ORDER CREATED**, from the full (or differential, if one was used) database backup forward to the time you want to recover to.  You CANNOT skip a log, and a log CANNOT be missing, or SQL won't be able to do the recovery.  

Often you'll be recovering to the last time possible, in which case you don't need to specify any specific time.  But if you want to recover to a specific point in time, specify "STOPAT" on the log restores.
0
 
LVL 25

Author Comment

by:Lionel MM
ID: 39164378
Thanks for the suggestions. However something that I can't explain happened. In addition to using SQL System Management to do backups I also have a script that run at 12AM each night. I shutdown SQL server and then copy all the files manually, then restart SQL. So when my databases crashed, actually at about 2PM on Friday the live files just disappeared, gone. (Thought I was hacked--checked everything and found nothing). So I took the files that I made a copy of the night before and manually stopped SQL and put them in the live directory. I then had the office manager go in to the program on Monday to verify that what we have was indeed the end of day on Thursday. In effect they had all the work they had done on Friday too. Is it part of the SQL process to "automatically" use TRN files to "auto-update" a SQL database? Otherwise how do I understand how files dated from 5/9 can include work from 5/10 when "all" I did was copy the 5/9 files from the backup location to the live location?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39165181
As part of recovering a database, SQL must either "forward recover" or "rollback" all pending changes on the log file.  That is, is a transaction started against the db but the completion of that transaction is not done against the db, SQL must finish the transaction: either the entire transaction must be completed or the entire transaction must be rolled back.  SQL must do this, because it's inherent to the definition of a "transaction": all or none of it must be applied.
0
 
LVL 25

Author Comment

by:Lionel MM
ID: 39165423
Scott--that makes sense but does that explain how a copy of a database that was last used at 6PM on 5/8 (wereh I would assume all pending changes are made to it), to be copied as a live database on 5/9 to get all the transactions done during 5/9 (till 2PM when it crashed )?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39165563
It doesn't.  That can't happen.  Something else happened in your specific situation.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 39166432
that makes sense but does that explain how a copy of a database that was last used at 6PM
What exactly did you copy?  Was it the data (typically has an "mdf" extension) files as well as the Transaction log (typically has an "ldf" extension) files?

Also and unrelated, you really need to reconsider this practice of shutting down the server in order to copy the database files.  That is what you have SQL backups for and without the risk of taking your database offline.
0
 
LVL 25

Accepted Solution

by:
Lionel MM earned 0 total points
ID: 39167763
I do this at night when no-one is there. I am old school and don't trust automated systems; I prefer scripts, to know that I have a real copy of what I know I have to have in case it crashes, and in this case that seems to have worked out great, although I still am unsure how all the work from 5/9 got applied. And yes I do copy the .mdf and the .ldf files (both of which were from 5/8).
0
 
LVL 25

Author Closing Comment

by:Lionel MM
ID: 39251063
Thanks for the help but this ended up being one of those things that miraculously resolved itself so I did not have to view the logs (the question posed). Thanks for your time.
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question