Hi, I am new to SQL server administration. Got a question about backups.

How can you recover your database @12:50 PM Monday if your last full backup is at 12:00 AM on Saturday and with no T-Logs ? How much space have you provided to the T-Log of your database ? Coz if your DB is in Full mode then your T-Log gonna fill up until you take backup and you are screwed.

To your question,

So why do i need to make periodical transaction log backups (DBAs

suggest every 15 mins after full backup), if i can restore to any point in time after full backup without making any periodical transaction log backups.

You can restore to that point in time ( in your case the last full backup as you do not have any T-log backups). So, if you do not have any T-log backups then you can restore only to that point ( last full back ) and you lost all the valuable data from that point. I think you are smart enough to know the value of data. :)

I made a full backup.I have not made any tlog backups since then. Then I go ahead and delete some rows. I go to restore and it lets me choose the time in the middle(if i want as recent as possible, i need to make tail log backup, but since I want say 1 hour from the deletion, i did not have to make tail log backup). After the restore, I checked the table, and it had sucessfully restored the rows i deleted earlier.

So, you are saying restoring the DB with the Full Backup ( taken saturday) , the changes you made afterwards was also seen - undoubtedly impossible.

You have to have T-Logs backups taken if you want a point in time recovery - Period..

 

My understanding is that enabling a full recovery mode is enabling the transaction log. In your case the data got restored looking from the transaction log.

You are right, you don't have to make a transaction log backup. If you don;t your ldf files keeps on growing. If that is not a concern then you don't need that to be backed up.

No, if you take sql t-log backup, it will continue writing to your log files, its just the check point was reset. If you have mutiple transaction log backups, you cannot miss anyone of those to restore them up to the point of failure.

 

another scenario...

yes khaiurey, thats what i think too. it looked at the original transaction log ! I just had to make the tial log backup right before restore and it allowed me to do restore to any point of time between full backup and point of failure.

I think, still you are getting those record restored from the log even though it was backed up.  If you have deleted the transaction log backup and try to restore it, then, I think they wont reappear.

In other words, sql is taking all your transaction log files as a single chunk while you are restoring. It is not just referring to the current log file. If you delete any one of the log backup in the sequence then you will not be able to recover anything after that sequence.
e.g
4:05 PM - Full_backup.bak, and your transaction log resets
4:10 PM - transaction log backup -log1.trn
4:11 PM - delete record 1
4:15 PM - transaction log backup - log2.trn
4:16 PM - restore at point 4:12 PM (record 1 will get restored), but if you have deleted log1.trn or log2.trn and try to restore at point 4:12 PM then record 1 will not get restored (you will not have option to restore at that point or it will error out or simply you can't restore).

So the concept you are thinking is right. Its just SQL is making a 'smart' decision to take into consideration all the transaction log backup while restoring.

I am not sql dba, just have worked little on its backup and log shipping/mirroring. I may be totally wrong. So please verify yourself.

And benefits of transaction log backup looks obvious to me. If you hadn't backed up transaction log and your single transaction log is corrupted then you will loose all the data after the last full backup. But if you have multiple file, then you wont loose the data until you loose the sequence of trn log. If your tail is corrupted, you can restore up to the last trn backup which could be just few minutes earlier. So you can restore up to any point your trn files are fit. Further trn backup is completed in much less time compared to full backup. So it is usually done every few minutes. The previous trn backup after the full backup is not needed. This way you are also preventing your log file from increasing indefinitely.
Hope I made you clear.

thanks khairey. could you also explain 'resetting the transaction log' part. Does it resets after full backup or periodic transaction log backups. So after I take my full backup, it truncates the transaction log? whats this truncating? some scenarious would be helpful

read this and other articles of transaction logs / truncation of transaction logs/ transaction logs architecture etc.
http://technet.microsoft.com/en-us/library/ms189085.aspx

thanks khairey. i am getting more hang of this.