September 28, 2007 at 8:42 pm
i want to ask a question about the principle of backing up data.
what's the benefit of backing up log files?
if we have the data, what we could need log files?
September 28, 2007 at 8:54 pm
The point is that you don't always have the luxury to take a full backup every 5 minutes like you can a tlog.
Also with a tlog, you can do a point in time restore. Ex :
Full back 9AM
Tlog back 9:30
You get a call at 9:35 that a developper accidentally dropped a table. You can restore the db as it was at 9:29 (just before the drop happened). Then you can go on about your daily work.
PS In this case you'd restore the db on another server and reimport the table so that you don't lose any other data from other tables... but that's another story.
October 1, 2007 at 2:13 am
FerasGer83 (9/28/2007)
i want to ask a question about the principle of backing up data.what's the benefit of backing up log files?
if we have the data, what we could need log files?
Ninja's_RGR'us is right. Full backups can use up much more space than log backups. Also, strongly related is the amount of time required for a full backup. If it takes half an hour, whereas a log backup would use a few minutes, and you would like to loose max one hour worth of transactions, taking hourly full backups will put a rather large load on your server/disks. Assuming your server is busy, using log backups in addition to less frequent full backups will lessen the load on your server.
Books Online is a good resource to get more info on this:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Regards,
Andras
October 1, 2007 at 1:37 pm
thank you .
what i got is that the downside of FULL Backup is the time it consumes from the server, so that we can't take a FULL backup anytime.
i read in this article about FULL backup the following statement:
This includes part of the transaction log so that the full database backup can be recovered
so:
why we need a part of the tranaction log to recover the backup, if we have the data, why can't the server just copy all data from the backup files?
what's the usage of transaction logs here?
October 2, 2007 at 1:43 am
FerasGer83 (10/1/2007)
thank you .what i got is that the downside of FULL Backup is the time it consumes from the server, so that we can't take a FULL backup anytime.
i read in this article about FULL backup the following statement:
This includes part of the transaction log so that the full database backup can be recovered
so:
why we need a part of the tranaction log to recover the backup, if we have the data, why can't the server just copy all data from the backup files?
what's the usage of transaction logs here?
The short answer to your last question: SQL Server can just restore the database from the full backup, and you do not have to apply the subsequent log backups. The advantage of using log backups as well, together with the full backup is that you can reduce the time you spend backing up, reduce the time for recovery from a disaster, while reducing the amount of period for which you loose data.
For example you take a full backup at midnight (say this takes 45 minutes), and log backups hourly (takes 5 minutes). At 6am your disks blow up. As you mentioned you can restore the full backup, and it will work perfectly. But since that backup was taken at midnight, and now is after 6am, you will loose the transactions in between. However, since you have the hourly log backups, you can restore the full backup, and then, restore the hourly log backups, and you may loose about an hour worth of transactions (or hopefully much less). Depending on the database of course, but usually log backups are lighter weight than full backups and take less time to back up the log.
An alternative approach to loose only max one hour of transactions (assuming you can notice and react immediately), is to take full backups hourly. (and every hour load your disks for 45 minutes).
I hope the above helped to understand the benefits of log backups. Everything depends on your requirements of course. (next step, you may want to read about differential backups)
Regards,
Andras
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply