January 13, 2005 at 9:45 am
Hi !
I need some better understanding about the full database back up and log file back up. I have one database. Main purpose of this database is to collect data (about 1500 to 2000 record per table every day).
I set up the complete database back up of tape drive about
I observe couple of days that my log file was getting too big around (1500 MB free space around 1200MB). I have to shrink log file every other day to shrink the file. To prevent the log file growth I am planning to set up the log file back 2 or 3 time a day (over write the same log file on hard drive) to prevent the growth of log file.
My question is what happens as soon as I do back up the log file only more often (Complete back up once a day) does it flush the committed data or I have to do full database back up first in order to flush the data from log file.
(Other way if I do only log file back up at 10 Am (log file was 250MB) does it flush the data and start filling from top or it keep growing from 251 MB until I do full database back up and then flush the log file and start from top) (In order to prevent the log file growth do I need to set up both complete (full) and log file back up more often or only log file back up 3 or 4 time can do that)
January 13, 2005 at 11:43 am
You don't need to do full backup because of more often logs backups. Data written to database file from log file every time when log backed up. So, before the next backup log file will have data since last log backup.
This is a good idea to do more often log backups to reduce its size. But sometimes you can have log file growth on database optimisation.
To restore database you will need full database backup and ALL log file backups. Let say, you have daily full backups and hourly log backups. Even if you miss full backup for today, you still can restore database with full backup from yesterday and all log backups since yesterday full backup to the last hour.
January 13, 2005 at 12:14 pm
Thanks for replay me back.
According to my understanding if you do log back up it transfered all committed data from log file to datbase file and truncket the log file.
Now if you have yesterday full back up file and you are doing hourly log file back up (Over write same log file ever hour) means every hour committed data transferd into current database. now you do last log back up at 11 Am and you have last full back up upto yesterday. Database get broke at 11:10.
You restore yesterday back up and now per my understanding current log file carried only info about uncommitted data from 11 to 11:10 AM or it also carried info about all committed data from yesterday's full back up. How does it work?
January 13, 2005 at 12:34 pm
I got better idea how does it work not. Only one question should i append the log file every hour insted of overwrite because if i overwrite then it delete first log file info right.
January 13, 2005 at 3:02 pm
Yes, you should append log backups (you need all log backups for restore). And about last log - it has data just for last 10 minutes from 11 to 11:10 AM.
January 14, 2005 at 1:13 am
Your log full problem doesn't occur because of cross posting does it ?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=155419
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=155416
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=155415
One message is usually enough to get an answer. Now I have to post this answer to all your threads....
Usually in a Production environment I backup logs every hour and do a full backup every day.
Logs record all the changes in the database that have occured. Backing up the log clears out the log, and then Sql server will reuse the space in the log file. If you don't backup the log file it will grow until you do back it up.
In order to restore a database, you have to restore a full backup first, then you can restore transaction log backups made after the full backup.
You need to have your transaction log backups APPEND otherwise they you won't be able to restore them. You could however initialise your tapes before the full backup. Obviously you need to rotate tapes for a complete backup plan, but that's a different issue.
As you said before, If you did a full backup every day at 12.00AM and hourly log backups... if the database was to die at 11:10, you could restore the full backup, then all the transaction log backups, which would bring you back to around 11:00.
Julian Kuiters
juliankuiters.id.au
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply