Solved

SQL Server 2000 Transaction Logs TRN Files

Posted on 2013-05-13
9
703 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:lionelmm
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:ScottPletcher
ScottPletcher 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 24

Author Comment

by:lionelmm
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Author Comment

by:lionelmm
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:ScottPletcher
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 24

Accepted Solution

by:
lionelmm 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 24

Author Closing Comment

by:lionelmm
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now