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


SQL Server Backups


SQL Server Backups

Author
Message
tafountain
tafountain
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 389
This is more of a poll but I'd like to get others opinions. What is the recommend and preferred methods of SQL Server backups considering all technologies:
- Native SQL Server backup to tape
- Native SQL Server backup to disk
- SAN backups (specifically for my purposes - NetApp Snapshots / FlexClones).

Go!
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10058 Visits: 6324
We do backups to local disk, then the backups are backed up via the SAN backup, they are also shipped to the virtual tape array via NetBackup and they are also replicated to the DR server, which is also backed up by the DR SAN backup and is also backed up to the DR virtual tape array.

So we have a multiple point of failure system where we can get the backup back.

Data recoverability is a big issue for us, as every hour we have an outage costs the company millions of pounds in productivity and lost revenue, so the quicker we can get things back should an issue or a failure at any point of the infrastructure should happen



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


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: 86757 Visits: 45254
It depends.

On DB size, backup windows, technology available, restore time window, etc.

I wouldn't backup to tape though. Not directly at least.

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


tafountain
tafountain
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 389
@Gila: I can provide you with my options but was looking for some general feedback first. Obviously I'd like to rule out options and not focus on them if they simply do not apply. But to elaborate here are additional details:

  • Just under 300 databases across 15 servers (with new servers on the horizon)

  • Each server contains a few to 20+ databases where the total size of all databases range from small to medium (few hundred MB up to just under 1 TB)

  • The largest databases are approximately 300ish GB. We're just now approaching the VLDB thresholds. My projections show by the end of 2015 we'll be over double our current size with our largest and most active databases.

  • Backup window, 3 hours for all - tops... I'd like to beat that by aiming for 1 hour

  • Options available native backups, tape backups, NetApp snapshots

  • Restore time, not defined but the expectation is ASAP... working on that one next



Let me also add our databases are currently versions 2005-2008. We also have SharePoint 2010 databases that will be folded into the mix so their backup is slightly different. In addition I want to try and get the backup strategies as consistent as possible across all servers / platforms.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62020 Visits: 19101
Disk is my vote, perhaps striped to multiple physical locations for speed. Then copy over to tape/DR.

The SAN stuff can work, but you better check this carefully. Not all SAN systems are transactional aware, and you might end up with a backup/copy that can't start.

Either way, you still probably need some log backups, unless you plan on losing data between the backups/snaps.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
tafountain
tafountain
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 389
Thanks for your input everyone. You've helped me confirm my initial direction is valid - backup to disk and then allow our infrastructure group backup the files to tape.

Currently our databases are backuped up to tape directly and there are all sorts of issues. It currently takes 9+ hours to do a full backup of all our databases (just under 1 TB). And that's only production. My new proposal is going to go straight to disk plus include a few additional optimizations (to name a few):
- Use compression where necessary
- Use multiple backup files (up to 1 per CPU)
- Separate backup traffic from the network traffic

I see no reason why we cannot backup 1 TB of data to disk in 30 minutes tbh. I based most of this on a case study for fast and reliable backup and restore of VLDB over the network.
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: 86757 Visits: 45254
tafountain (11/19/2012)
Use multiple backup files (up to 1 per CPU)


I wouldn't necessarily recommend this.
If you're going to stripe your backups, stripe based on how many IO paths you have (if you have 2 backup drives that are separate physical drives or separate IO channels, then stripe to 2 files).
Backup is an IO-bound operation, not a CPU-bound, if you stripe to multiple files on the same drive you probably won't see much of a gain. Only add multiple backup destinations if you have multiple separate IO paths (so 32 cores, 32 destinations on 1 LUN isn't a good idea. 32 cores, 32 destinations across 16-32 LUNs or 32 cores, 16 destinations across 16 LUNs may work)

Separate backup traffic from the network traffic


Backing up to disk or backing up to a network location?

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


vikingDBA
vikingDBA
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
Just to add my scenario, I have multiple SQL Servers (approx. 8), with a few databases on each, not big, largest db is 25 GB. (One Oracle DB is 115 GB.)

For SQL Server, I backup straight to disk which is on-server (preferrably the non-data disk, but can't always be helped). I wrote a .net program to take a list of directories, and compare the first directory with the throw-over directory. Any files not already in the throw-over area, copy them. This runs every hour. The throw-over goes to a Data Domain dedupe machine 1/2 mile away. You could use a NAS if needed, though it won't dedupe, but is a LOT cheaper.

I try to keep 2 days' worth of full and tranlog backups on server, with (by policy) 2 weeks' worth on the Data Domain. (I also wrote a program to go in each directory and delete files over X days old, using either Windows create date or SQL Server file timestamp date, can set different length of time for each directory.) This keeps any needed restores on disk for 2 days. Any needed from longer than that can just be copied back over to the server from the Data Domain.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19828 Visits: 17242
GilaMonster (11/19/2012)
Only add multiple backup destinations if you have multiple separate IO paths (so 32 cores, 32 destinations on 1 LUN isn't a good idea. 32 cores, 32 destinations across 16-32 LUNs or 32 cores, 16 destinations across 16 LUNs may work)

also, the multiple drives should have the same I\O capability\performance ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
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: 86757 Visits: 45254
Perry Whittle (11/20/2012)
GilaMonster (11/19/2012)
Only add multiple backup destinations if you have multiple separate IO paths (so 32 cores, 32 destinations on 1 LUN isn't a good idea. 32 cores, 32 destinations across 16-32 LUNs or 32 cores, 16 destinations across 16 LUNs may work)

also, the multiple drives should have the same I\O capability\performance ;-)


That should fall into the category of 'common sense'.... not that common sense is all that common. :-)

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


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