Avatar of NytroZ
NytroZ
Flag for United States of America asked on

SQL 2005 Transaction Logs

I was reviewing our SQL DB backups and noticed that the DB's marked for full recovery do not have  any transaction logs.  I am looking for a .trn file associated with the db and cannot find any.  I thought these were necesaary in order to do a point in time recovery.  Where is the default location of the transaction logs?  I worked with a dba in hte past that used to get trn files every 15 minutes and would back them up hourly.  How was this accomplished?  My goal is to be able to back up a db and allow for recovery within 15 minutes of a failure.  Any help doing this through SQL Management Studio would be great.
Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
lcohan

Try findout the backup history and I would do it the easiest by connecting to the SQL DB in SSMS, right click the DB, select Restore but DO NOT !!! hit ok to restore!!!

In the window that pops up you should be able to see the info you are looking for.
NytroZ

ASKER
I see th elocation of the ldf file but I thought that assigning "full" recovery mode created trn files as well.  Am I mistaken?  I can't find any transaction log files anywhere on the storage device nor any setting that would lead me to them.
lcohan

<<I see th elocation of the ldf file but I thought that assigning "full" recovery mode created trn files as well.  Am I mistaken? >>

Yes, "trn" files are created by a SQL Server Backup Maintenace Plan.

<< I can't find any transaction log files anywhere on the storage device nor any setting that would lead me to them. >>


you can find them by running query below against your database:

Use your_db_name
go

select * from sys.database_files

and physical_name will reveal the location of the transaction log files OR in SSMS just right click the DB and check Properties.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
NytroZ

ASKER
I have a db in full recovery mode.  I have a full backup of this database fro 10pm last night.  If the db wer to become corrupt at 1 pm this afternoon, shouldn't I be able to restore the db closer to 1pm this afternoon than 10pm last night?  i can understand how to restore from the full backup but I don't understand where the transaction logs are to restore the changes between 10 and 1.
NytroZ

ASKER
Ran query but no sign of any trans logs.  Only the mdf and ldf.  I'm obsiudly missing something right?
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
NytroZ

ASKER
I think I see what you are talking about.  The dba must have created a job that backed up the ldf on a set interval which created a trn?  If this is right, how do you go about doing something like that?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

If this is right, how do you go about doing something like that?
Take the time to re-read all the comments and you will see that lcohan answered that very question more than once.  But here it is again:  If you not experienced using SQL Server than use a Maintenance Plan to create a scheduled Transaction Log backup.  This needs to run frequently, it could be anywhere between every few hours to ever 15 minutes.