backup of database

Question

Post reply

backup of database

ramyours2003

SSChampion

Points: 12268
More actions
November 12, 2014 at 8:02 am

#397635

I have 15 TB of database I want to complete the full backup of the db in less time how can we achieve this ?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

1. Use backup compression

2. Stripe your backup across multiple files

3. Change the BUFFERCOUNT

4. Change the MAXTRANSFERSIZE

As always test first.

You might want to be looking into SAN Snapshot backups for something of that size.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Jack Corbett (11/12/2014)
1. Use backup compression
2. Stripe your backup across multiple files
3. Change the BUFFERCOUNT
4. Change the MAXTRANSFERSIZE

5. Move the data files to faster drives (permanently)

6. Put the backup on faster drives

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

Regardless of what you do to modify the backup, and I like the suggestions, make darned sure you test the restore to ensure you both know how to do it and that it works. Backups are just a file (or collection of files if you stripe them) until you can restore them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL Guy 1 SSCoach Points: 15981 More actions · Answer 4

Is your database partitioned? With the size of 15 TB I assume that yes, but if not, you can consider partitioning large tables with setting old partitions to read-only. You can also have non-partitioned filegroups to be read-only.

In this case, for first time you will make Full Full backup, and after that Partial Full (it will backup only read-write filegroups), until the next switch of "current" to "archived" filegroup in a partition scheme.