February 7, 2012 at 2:31 am
Backups to Disk. From what I have read and seen it looks like good practice to do SQL backups to Disk and then back this off to tape soon after. Currently we do not do this but use agents to backup directly to tape. What arguments should I be using to convince the ‘Infrastructure’ teams that it is better to go to disk first?
Thanks for your help
February 7, 2012 at 2:36 am
I would suggest backup to disk for these two reasons:
1. Backup time faster
2. Restore time faster
You can replicate these backupps to tape outside of the SQL Backup process and store these offsite.
Rob
February 7, 2012 at 4:11 am
Best practice to backup to disk.
This keeps the most recent backup available for a speedy restore. How often do you need last weeks backup?
Tapes are also slower in general.
February 7, 2012 at 6:03 am
If you backup to disk then offsite this backup to tape this also gives you two copies of the backup.
you keep perhaps 3 on disk for speedy recovery and a larger number on tape for DR purposes (loss of backup drive) and recovery further back in time.
Recoveries from disk are almost always more reliable, plus it puts database recovery under the control of the DBA. Recovery from these agents that go direct to tape can be problematic. Have you tested recovering one of these backups (especially to point in time)?
---------------------------------------------------------------------
February 7, 2012 at 7:03 am
For all your comments so far, Thank You.
I have done test restores of some systems using these tape based backups, including P.I.T. and they have worked but are not always easy to configure. They have to be done via the agents GUI which is not as intuitive as it could be. Unfortunately we have an older version of the application so it has no support for SQL compression. Also at the vendors recommendation, we only stream to a single LTO3 drive If we could split the backup over multiple paths I am sure this would speed things up too.
Currently one of our main production db's (SQL 2K5 soon to move to 2K8) is 1.7Tb and take around 6.5hrs to backup. As I currently have no environment to restore this to I do not know the restore time but I am told a rule of thumb is 1.5 time the backup time. Any ideas how quick such a database could be backed up to SAN disks?
We would save on the cost of the agents which is not a small figure this benefit can go into the mix too.
February 7, 2012 at 7:49 am
PAH-440118 (2/7/2012)
Currently one of our main production db's (SQL 2K5 soon to move to 2K8) is 1.7Tb and take around 6.5hrs to backup. As I currently have no environment to restore this to I do not know the restore time but I am told a rule of thumb is 1.5 time the backup time. Any ideas how quick such a database could be backed up to SAN disks?
.
Are you using any 3rd party tools to compress and speed up back ups? I've got a 5TB database that backs up in 6 hours using Litespeed. It restores in approx 5 hours too.
February 7, 2012 at 8:17 am
We are not using any compression tools as I assumed (wrongly?) that these would not integrate with the SQL backup Agents we are currently using to go directly to tape, thereby requiring us to go to disk first.
When we go to SQL 2K8 compression is built in. Do the like of LiteSpeed give any benefits over this?
February 7, 2012 at 8:21 am
I am sure with LiteSpeed or Other backup compression tools, you can backup to tape (to go with your current choice of tape backups) and at the same time you can also configure striped backups (backup to multiple files) resulting in less time for the backup..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 7, 2012 at 8:23 am
PAH-440118 (2/7/2012)
Do the like of LiteSpeed give any benefits over this?
If I am not wrong, LiteSpeed has an option to restore a single object from a backup, though SQL 2008 or above provides backup compression, it does not allow you to restore a individual tables..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 7, 2012 at 11:46 am
Bru Medishetty (2/7/2012)
PAH-440118 (2/7/2012)
Do the like of LiteSpeed give any benefits over this?If I am not wrong, LiteSpeed has an option to restore a single object from a backup, though SQL 2008 or above provides backup compression, it does not allow you to restore a individual tables..
It does but I think its enterprise only
February 7, 2012 at 11:47 am
PAH-440118 (2/7/2012)
We are not using any compression tools as I assumed (wrongly?) that these would not integrate with the SQL backup Agents we are currently using to go directly to tape, thereby requiring us to go to disk first.When we go to SQL 2K8 compression is built in. Do the like of LiteSpeed give any benefits over this?
There's noting to stop you backing up to disk or tape directly. As mentioned before, its considered normal to backup to disk first and then its just another file for the tape backup.
February 7, 2012 at 11:48 am
PAH-440118 (2/7/2012)
When we go to SQL 2K8 compression is built in. Do the like of LiteSpeed give any benefits over this?
My experience with the compressions ratios suggest litespeed compression is a little better and faster
February 7, 2012 at 12:41 pm
when you get into terrabyte databases the emphasis shifts (IMHO) and direct backups to tape begin to make more sense. Thats a lot of disk to tie up for backups
However as stated as you are moving to SQL2008 native compression becomes available to you. compression ratios of 6-8 times are normal. If you go to SQL2008 you need enterprise edition, However in 2008R2 compression is available in standard edition.
compression is available via 3rd party tools but if all you want to do is compress why go to the expense and change in backup strategy?
---------------------------------------------------------------------
February 7, 2012 at 3:15 pm
george sibbald (2/7/2012)
when you get into terrabyte databases the emphasis shifts (IMHO) and direct backups to tape begin to make more sense. Thats a lot of disk to tie up for backups
I used to think this, and it's still true somewhere, but the cost of storage is low enough for tier2-3 that I'm not sure a 1TB database needs to go to tape now.
If you're at 10TB, probably, but at that level you might really be looking at SAN backups, snapshotted and sent block by block to another device because you won't complete a backup in a reasonable time.
February 7, 2012 at 3:44 pm
Steve Jones - SSC Editor (2/7/2012)
george sibbald (2/7/2012)
when you get into terrabyte databases the emphasis shifts (IMHO) and direct backups to tape begin to make more sense. Thats a lot of disk to tie up for backupsI used to think this, and it's still true somewhere, but the cost of storage is low enough for tier2-3 that I'm not sure a 1TB database needs to go to tape now.
If you're at 10TB, probably, but at that level you might really be looking at SAN backups, snapshotted and sent block by block to another device because you won't complete a backup in a reasonable time.
Likewise. I still backup a 5tb+ database to disk (1xfull, 6xdiff)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply