SQL Server 2000 Transaction Logs TRN Files

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.
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
Who is Participating?
 
Lionel MMConnect With a Mentor Small Business IT ConsultantAuthor Commented:
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
 
Psihawk99Connect With a Mentor Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
It doesn't.  That can't happen.  Something else happened in your specific situation.
0
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.