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


SQL Server Backups


SQL Server Backups

Author
Message
Tony Fountain
Tony Fountain
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: 1063 Visits: 410
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 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: General Forum Members
Points: 62472 Visits: 8591
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



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
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 (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)

Group: General Forum Members
Points: 546818 Visits: 47732
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


Tony Fountain
Tony Fountain
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: 1063 Visits: 410
@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 (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)

Group: Administrators
Points: 327841 Visits: 20091
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
Tony Fountain
Tony Fountain
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: 1063 Visits: 410
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 (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)

Group: General Forum Members
Points: 546818 Visits: 47732
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

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

Group: General Forum Members
Points: 136086 Visits: 18193
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 (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)

Group: General Forum Members
Points: 546818 Visits: 47732
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