December 8, 2008 at 2:17 pm
In my scenario, I do full database backups every night. I'm wanting to have some sort of recovery of the day's work, say every 15 minutes.
I noticed in the Maintenance Plan, Backup Database Task is a Backup Type option for Transaction Log.
In the database properties is a Transaction Log Shipping option.
What is the difference between the two? If there is a difference, what considerations would I ask for either one?
Or should I just use a Differential Backup in the Backup Database Task?
December 8, 2008 at 2:25 pm
Transaction log and Transaction log shipping are two different concepts. Transaction log shipping is used in high availability and Transaction log is the part of a plan used for disaster recovery.
The best repository for this is books online and before changing the recovery models for any database consult your supervisor.
December 8, 2008 at 2:31 pm
this is a wider topic to discuss here. Transaction log backups are those which you can backup from time to time so that you can recover the database to the point of failure when a disaster occurs.
http://databases.about.com/od/sqlserver/a/disaster_3.htm
while transation log shipping is used to provide alternative database for high availability in case if there is any failure of the primary...
this woul dbe helpful:
December 8, 2008 at 2:38 pm
So log shipping is only for keeping a database on another server up to date with the primary.
In my case I just want to create a transaction log backup job.
Thank you!
December 8, 2008 at 2:39 pm
fizzerchris (12/8/2008)
In my scenario, I do full database backups every night. I'm wanting to have some sort of recovery of the day's work, say every 15 minutes.I noticed in the Maintenance Plan, Backup Database Task is a Backup Type option for Transaction Log.
In the database properties is a Transaction Log Shipping option.
What is the difference between the two? If there is a difference, what considerations would I ask for either one?
Or should I just use a Differential Backup in the Backup Database Task?
What you want is to do Transaction Log backups every 15 minutes. Differential backups will back up all changed database extent pages since the last full backup (even if it's already been backed up by another differential backup) These would come in handy for example if you did a full backup overnight, did your transaction log backup every 15 minutes, and a differential backup at noon, then if you had a failure in the afternoon, you'd only have to restore the full backup, the differential backup from noon, and then the few transaction log backups between noon and the failure. Without the differential backups, you'd have to restore all of the transaction log backups since the full backup.
December 9, 2008 at 10:08 am
To me
Log Shipping = Trans Log backup + Scheduled restore
so if you want High Availability or warm standby, use Log Shipping (which also gives you log backup at the same time)
If there's no other server in question, just do log backup for safety restores
DIFF backup can be done regardless without breaking the log chain
Typical setup, say (max. 1 hour data loss)
Weekly FULL backup
Daily DIFF backup
Hourly TLOG backup
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply