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).
Backing up directly to tape is deprecated in T-SQL. I don't believe it's recommended by Microsoft, and no DBA I know of recommends it.
We back up to a local drive and use 3rd party compression to save additional space, and then copy over to a network share. We are currently adding offsite backup as a "tape" option, but all our on-site backups are kept on network shares.
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.