SQL 2005 Transaction Logs

NytroZ
NytroZ used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
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.

Author

Commented:
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.
lcohanDatabase Analyst

Commented:
<<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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.

Author

Commented:
Ran query but no sign of any trans logs.  Only the mdf and ldf.  I'm obsiudly missing something right?
Database Analyst
Commented:
<<Ran query but no sign of any trans logs>>

Yep - .ldf ARE sql transaction log files.
.trn ARE SQL transaction log backups produced by a SQL backup maintenance plan


http://stackoverflow.com/questions/826789/what-is-the-ldf-file-in-sql-server

Author

Commented:
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?
Top Expert 2012

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial