Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Backup and transaction log Expand / Collapse
Author
Message
Posted Friday, May 8, 2009 1:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 7:02 AM
Points: 1,602, Visits: 1,571
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//
Post #712662
Posted Friday, May 8, 2009 1:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #712665
Posted Friday, May 8, 2009 1:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 7:02 AM
Points: 1,602, Visits: 1,571
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//
Post #712670
Posted Friday, May 8, 2009 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
Exactly: the transaction log backup is taken from the last backup, either full or tlog.

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #712671
Posted Friday, May 8, 2009 1:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 7:02 AM
Points: 1,602, Visits: 1,571
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//
Post #712676
Posted Friday, May 8, 2009 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #712681
Posted Friday, May 8, 2009 1:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 7:02 AM
Points: 1,602, Visits: 1,571
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//
Post #712695
Posted Friday, May 8, 2009 2:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 42,454, Visits: 35,508
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 2008, MVP
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

Post #712701
Posted Friday, May 8, 2009 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #712703
Posted Friday, May 8, 2009 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #712709
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse