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


SQL Server Backups


SQL Server Backups

Author
Message
tafountain
tafountain
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22970 Visits: 6519
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 (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211583 Visits: 46258
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
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 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 (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: Administrators
Points: 139514 Visits: 19413
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
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 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 (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211583 Visits: 46258
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1144 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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50734 Visits: 17653
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 (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211583 Visits: 46258
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