May 8, 2009 at 1:03 am
Hi all,
Good morning...
I have some doubt regarding backup and transaction log. While taking the transaction log, how should we take transaction log backup, should I append every time with the existing one or overwrite it? I want the recovery upto the point of failure.
Example:
Say I have taken full backup at 8:00. After that I have done some transactions and taken transaction log backup at 9:00. Again doing some transaction I have taken transaction log backup at 10:00. Transaction log backup has been taken with the option overwrite each time. My question is that If I restore the databse with the full backup and the transaction log backup taken at 10:00, can I see the changes done between 8:00 to 9:00?
How does the transaction log backup keeps information? Does it keep the information from the last transaction log backup?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 8, 2009 at 1:07 am
If you want to restore the database up to the failure time, you will have to restore all the transaction log backups you took since last full backup. You can append to the same media (I don't recommend this) if you want.
Regards
Gianluca
-- Gianluca Sartori
May 8, 2009 at 1:15 am
Thanks for the reply....
So how does the tranction log back up keep record? Is it the transactions from the last backup?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 8, 2009 at 1:20 am
Exactly: the transaction log backup is taken from the last backup, either full or tlog.
-- Gianluca Sartori
May 8, 2009 at 1:36 am
Thanks...
Is the same things happen for the differential backup? Like from the last full backup/ differential backup?
Can you please tell me the standard plan(taking full,differential, transaction log) for taking backup of a critical database? For full backup is it necessary to take backup of all the system databse along with the user database?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 8, 2009 at 1:43 am
The plan I prefer for backups is a nightly full backup and transcation log backup every hour, but it depends on your database size, user activity etc.
This fits my needs, I don't know if it fits yours.
I don't like differential backups, because I don't want to restore thousands of backups when I need to get back.
Hope this helps
Gianluca
-- Gianluca Sartori
May 8, 2009 at 1:58 am
Hi again,
For full backup is it necessary to take backup of all the system database along with the user database?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 8, 2009 at 2:04 am
Gianluca Sartori (5/8/2009)
Exactly: the transaction log backup is taken from the last backup, either full or tlog.
Log backups contain the log records since the previous log backup. Full and diff backups do not truncate the transaction log. The only time a log backup will contain log records since a full, is if that's the first log backup done after setting the DB into full/bulk logged recovery.
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
May 8, 2009 at 2:06 am
If your purpose is to restore the whole db server (to protect yourself from hardware failure or that kind of disaster) you will have to backup system databases too. Obviously you don't need to backup tempdb.
-- Gianluca Sartori
May 8, 2009 at 2:16 am
GilaMonster (5/8/2009)
Gianluca Sartori (5/8/2009)
Exactly: the transaction log backup is taken from the last backup, either full or tlog.Not quite.
Log backups contain the log records since the previous log backup. Full and diff backups do not truncate the transaction log. The only time a log backup will contain log records since a full, is if that's the first log backup done after setting the DB into full/bulk logged recovery.
Thanks Gail, once again you're right. (Fortunately) I don't have to play with backup/restore that much, so I tend to forget important things like this.
-- Gianluca Sartori
May 8, 2009 at 2:27 am
Gianluca Sartori (5/8/2009)
I don't like differential backups, because I don't want to restore thousands of backups when I need to get back.Hope this helps
Gianluca
If you have a backup strategy involving full,diff,tlog backups wont the differential backup help to restore lesser number of log backups.
"Keep Trying"
May 8, 2009 at 2:44 am
Hi,
What does differential backup capture? I mean does it take the changes from last full backup/last differential backup. So for taking differential backup is it necessary to append each time with the existing one instead of overwriting?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 8, 2009 at 3:03 am
Agree with Chirag, the differential backup helps to restore lesser number of log backups. On few of our servers where the backup size is big and we have disk crunch also. We take weekly full backup, nightly diff backup and hourly log backup. So diff backup has its own importance. It captures the changes from last backup. In case of recovery we need to restore full backup, last diff backup and tran log backups(after the diff backup) for the point recovery.
May 8, 2009 at 3:16 am
It depends on your DB size. We have small databases (under 100 Gb) and we prefer nightly full backups to keep it simple and reliable. We use Tivoli Data Protection for SQL Server and restoring from full + log is a one-click operation, so I don't mind restoring tlogs.
-- Gianluca Sartori
May 8, 2009 at 3:26 am
Hi,
I am taking full backup everyday 23:00 and overwriting it with the existing one. Transaction log every one hour and appending it every hour to get to get all the logs. So transaction log is getting increased. Is it good to delete the whole tranlog after the full backup and creating new one every day? What is the best way to do?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply