What can I do to speed up my production daily full-backup?

Question

What can I do to speed up my production daily full-backup?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

Jeff Moden (10/29/2015)
Jayanth_Kurup (10/29/2015)
http://blogs.msdn.com/b/sqlcat/archive/2008/03/02/backup-more-than-1gb-per-second-using-sql2008-backup-compression.aspx
Interesting read
What's "interesting" is that it would appear (I may have misread it) that they're putting the backups on the same system as the database and that would not be a good thing I don't care how fast it is.

What if they're just staging the backups to DAS and then copying them?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Kristen-173977 (10/30/2015)
Jeff Moden (10/29/2015)
I've got a major table that contains call recordings divyed up like that (almost 400 gb) and I have one filegroup and file per month. Once a month goes by, I do a final backup on the month and set the filegroup to READONLY. As a bit of a sidebar, the auditors LOVE that.
...
Done right, it's not a trivial task. It takes careful planning to avoid storing a whole lot of blank disk space in read-only files. You DO have to make a plan and it DOES have a lot of caveats, but it's worth every minute of research and planning.
Any documentation on that Jeff? We don't really (I think?!!) have big enough tables to warrant that approach, but I would certainly appreciate seeing a "worked example" of some sort 🙂

Absolutely. Please see the following "attachment" link. The PPT runs you through it all and the code (numbered in the correct order) builds an example 4 million row table and demonstrates how to partition it including the monster trick of doing a final index rebuild without there being any free space left in the filegroup so you're not saving empty space in a READ-ONLY filegroup.

http://www.sqlservercentral.com/Forums/Attachment17585.aspx

It's a two hour presentation that I give now and again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 3

This article defines many options to speed up the backup, maybe it will help

http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

sgt500 Default port Points: 1413 More actions · Answer 4

i use this settings:

SKIP, NOREWIND, NOUNLOAD, COMPRESSION

BUFFERCOUNT = 2200

BLOCKSIZE = 65536

MAXTRANSFERSIZE = 2097152

my 8hours backup (1Tb compressed) now is done in 1:20Hours, at 800mb/s

check memory usage before set this params, level it by steps