SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup and transaction log


Backup and transaction log

Author
Message
Ryan007
Ryan007
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1854 Visits: 1595
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//
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9828 Visits: 13350
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Ryan007
Ryan007
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1854 Visits: 1595
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//
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9828 Visits: 13350
Exactly: the transaction log backup is taken from the last backup, either full or tlog.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Ryan007
Ryan007
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1854 Visits: 1595
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//
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9828 Visits: 13350
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Ryan007
Ryan007
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1854 Visits: 1595
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//
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86881 Visits: 45263
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9828 Visits: 13350
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9828 Visits: 13350
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search