Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

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.
0
Lionel MM
Asked:
Lionel MM
5 Solutions
 
Psihawk99Commented:
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 PletcherSenior 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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior 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 PerkinsCommented:
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:
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now