October 13, 2010 at 10:34 pm
hi,
the database is in full recovery mode. what is simple process to backup the database?
i want to perform full backup once a week and differential everyday.
i know we can set this through management studio.. but I dont know when to take the backup of the log file?
is everytime i perform backup regardless full or differential?
Thank you
October 13, 2010 at 10:45 pm
The frequency of the transaction log should be dependent on your service agreement... how much data can you afford to lose, or have to re-enter? On our systems, we have it set up for every 15 minutes.
Note that since your database is in the full recovery model, without backing up the transaction log it will continue to grow until all disk space has been occupied. You must do a transaction log backup to keep it at a manageable size.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 10:53 pm
in addition to what Wayne said, if possible, get these backup files moved to tape everyday having longer retention period.
i am facing corruption issues at the moment on 5 servers and i have backups!!
October 13, 2010 at 11:27 pm
so taking the backup of the log file will truncate the log file automatically? my plan was to once in a month, when the size grows shrink the log file using dbcc command
I understand the importance of the file. what I am trying to understand here is backing up the log is compulsory when you have a full recovery model database?
say I am happy to loose the data as long as I can restore to the point of last full backup /differential backup. in this case do i need to backup log file?
October 13, 2010 at 11:52 pm
rockymiho (10/13/2010)
so taking the backup of the log file will truncate the log file automatically? my plan was to once in a month, when the size grows shrink the log file using dbcc commandI understand the importance of the file. what I am trying to understand here is backing up the log is compulsory when you have a full recovery model database?
say I am happy to loose the data as long as I can restore to the point of last full backup /differential backup. in this case do i need to backup log file?
Backup log is the ONLY way to truncate ur log and keep the log size to a manageable size when ur db is in full recovery mode(so in a way it is compulsory or else it will keep on growing and fill ur disk). If you can afford data loss, why keep the recovery model to FULL? keep it simple and sql server will take care of the log size and truncation.
October 14, 2010 at 12:09 am
ok that make sense.
now consider this scenario
sunday full backed up 12:01 am
sunday tlog backedup up after the full backup is completed
monday tlog backed up at 10:00 am
monday differential backed up at 12:01 pm
monday tlog backed up after the diff backup is completed.
same for tuesday..wed...
and now thu before it makes the diff backup, database crashed. now its time to restore.
so at this point we have full backup from sunday, tlog from sun,
mon morning tlog
mon afternoon diff backup
mon after tlog
tue morning tlog
tue afternoon diff backup
tue afternoon tlog
wed morning tlog
wed afternoon diff backup
wed afternoon tlog
thu morning tlog
how we will be restoring now?
October 14, 2010 at 12:14 am
rockymiho (10/14/2010)
ok that make sense.now consider this scenario
sunday full backed up 12:01 am
sunday tlog backedup up after the full backup is completed
monday tlog backed up at 10:00 am
monday differential backed up at 12:01 pm
monday tlog backed up after the diff backup is completed.
same for tuesday..wed...
and now thu before it makes the diff backup, database crashed. now its time to restore.
so at this point we have full backup from sunday, tlog from sun,
mon morning tlog
mon afternoon diff backup
mon after tlog
tue morning tlog
tue afternoon diff backup
tue afternoon tlog
wed morning tlog
wed afternoon diff backup
wed afternoon tlog
thu morning tlog
how we will be restoring now?
Generally the frequency of t-log backups is 15 mins/30 mins/1 hr depending on how much you company can afford data loss.
coming back to this scenaio, you restore last sunday's full backup, followed by latest available diff backup(wed) followed by all t log backups taken after last diff backup.
plus, try taking tail-log backup of the corrupt database (if log file is accessible) and restore it at the end.
pls go through books online to understand the concept.
October 14, 2010 at 12:20 am
thanks pradeep.. that clears my confusion.. I did go through the books but got confused after reading so many articles about the backup.
Now I understand. thank you so much everyone:)
October 14, 2010 at 1:54 am
Thanks for the feedback.
October 14, 2010 at 2:32 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2010 at 3:07 am
thansk Gail Shaw. that was good explaination of transaction logs.
now coming bak to my scenario:
once I schedule full backup everyweek, diff backup everday and tlogs everyday twice...
sun full backup
mon diff
tue diff
database crashed and needs to restored.
in this case i need only sun full backup, tue diff backup and transaction logs performed after tue diff backup and before the crash.. is that correct? if it is then is it safe to delete all the transaction logs backup and differential backup occured before the tue diff backup and after the full backup?
and also next week when full backup is perfomed again, there is no point of keeping the previos full backup and the previos transaction logs?
does it make sense?
October 14, 2010 at 3:24 am
rockymiho (10/14/2010)
in this case i need only sun full backup, tue diff backup and transaction logs performed after tue diff backup and before the crash.. is that correct?
thats correct.
if it is then is it safe to delete all the transaction logs backup and differential backup occured before the tue diff backup and after the full backup?
and also next week when full backup is perfomed again, there is no point of keeping the previos full backup and the previos transaction logs?
No it is not safe to do so. Take a scenario - one of the users deleted an important table and intimated u after 2 days and requests you to do restore prior to that action. in that case you need to have all the backups.
Another scenario - If your database becomes corrupt and you come to know about it after few days, you need to restore from a past backup.
October 14, 2010 at 3:43 am
perfect.. normally I will be retaining backups for a month or may be for a year depending the company policy so we can restore to any time required.
the only reason I raised this scenario was assuming database is not corrupted and taken for granted we will be needing the data only from the latest backup or take it in other words for DR plan.
so if any disaster happens (eg building collapsed and had to move to new building), then just restor the last full backup, and the latest differential backup and all the t-logs after the latest diff backup.
thank you. now i can come up with some good backup strategy with all your help. much appreciated:)
October 14, 2010 at 5:22 pm
alrite after running the jobs (backup, diff, transaction logs) I was in impression that taking the t-log backup will shrink the log file automatically. but doesn't look like it. do I need add another step in the process to shrink the log file after the transaction log is backed up?
October 14, 2010 at 7:28 pm
It does not shrink the file; it marks the areas in it as available for reuse. If you're taking regular transaction log backups, it should grow to a point, and then be relatively stable there. Shrinking the file will just cause it to grow again to that stable level.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply