Sql-server – About backing up Transaction logs

sql servertransaction

I have understood the importance of transaction logs but still unclear on a few things:

  1. I've taken back-up of a T-log using this statement:

    BACKUP LOG myDB
TO DISK = 'I:\Log_Backup\myDB.TRN'
GO

Then i used the following statment to monitor the space used:

DBCC SQLPERF(LOGSPACE);
GO

Log spaced used which was previously large went down to 0.859 %

Now on the issue of backing up i have two questions:

  1. Our TSM guy is backing up the folders: I:/Database and I:/Logs thrice a day. I:/Database is the folder with all the MDF-files and I:/Logs is the folder with all the LDF-files.
    Shoud he instead be backing up I:/Log_Backup/ and I:/Logs?
    Or should he be backing up only I:/Logs where all the actual logs reside.

  2. In case of a disaster when i need to retore a DB, should i use the last back-up from I:/Log_Backup/ or from I:/Logs

Best Answer

  1. What TSM guy is doing is taking File level backup of mdf and ldf file which does not guarantee point in time recovery. You are correct to say that he should also be backing up folder(s) which holds transaction log backup or other similar backups i.e I:/Log_Backup.

  2. In case of disaster you would always want zero data loss or as much less data loss as possible. If you follow what TSM guy is doing you would be only able to attach database to point when it was Snapshot backed up. But with full backup and log backup you could do point in time restore(depending on level of disaster/corruption) and that would give you no or minimum data loss.

What you can do is you can ask TSM guys to also backup the folder holding full and transaction log backups.

Related Question