backups and transaction logs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

backups and transaction logs

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2007-06-25 : 07:23:45

Can someone clarify something for me regarding a complete backup and transaction backup.

If i take a complete backup once a day at 00:00 and transaction backups every 10mins, i wont be able to restore to a point after the full backup at 00:00 until the next full backup.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-25 : 07:27:58

with all transaction logs valid you can restore to a point in time by
first restoring a full backup and then restoring each transaction log to it.

or have i misunderstood your question?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rico_bano
Starting Member

35 Posts

Posted - 2007-06-25 : 07:34:22

no i think your understanding correctly

If a full backup is completed at 00:00 and the transaction log is backed up every 10 mins. if an error occurs at 01:33 i can restore to this point by first restoring the full backup and then restoring the transaction log up until 01.30

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-25 : 07:39:27

yes.

if you also want those last 3 minutes you have to take a final transaction log back up after the error.
a final tran log backup after an error occurs is also sometimes called a tail-log backup.
then you simply resore all.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rico_bano
Starting Member

35 Posts

Posted - 2007-06-25 : 07:52:55

Thanks for that. im finding the backup options a little confusing. I thought to do what i described ablove you would have run periodic differential backups after a full backup.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-25 : 07:57:41

yes yo can do that too.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rico_bano
Starting Member

35 Posts

Posted - 2007-06-25 : 08:03:05

Interesting.

So what is the recommended way to acheive what i would like to achieve.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-25 : 08:07:02

usually one full backup at day start, 2 diff backup during the day, 10-15 minute tran log backups in between.

of course this depends on your need for keeping latest data etc...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rico_bano
Starting Member

35 Posts

Posted - 2007-06-25 : 08:08:37

Thanks for that.

I take it to restore i would restore the full backup first, then the diff backup and lastly the tran backup.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-25 : 08:30:04

yes

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-06-25 : 08:31:12

"I take it to restore i would restore ..."

See also a worked example here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,Restore%20Full%20and%20all%20TLogs%20backups,Restore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources