Speeding up SQL DB Backup?

Question

How can you speed up a backup for a SQL database? When the files are big and many, it takes ages!

Answer 1

Most professional support staff have tools that automate the back-up process .. these are usually timed to run back-ups overnight, so 'who cares how long it takes ?'

If you have to be present eg. to change tapes (because you are exceeding single tape capacity and do not have an auto-tape changer) or the back-ups are still running when the business opens in the morning, then you have a problem ..

I assume you already only do full back-ups over the weekends and do 'incremental' backups during the week ??

Check which tables are changing during the incremental back-ups and consider 'archiving' any non-changing tables .. (so you don't keep including them in the full back-up) .. I also seem to recall SQL has a 'compress tables' option (this would eliminate the often massive number of 'zero filled' records)

If all else fails, you might want to consider duplicating your existing hard disk storage system - and use SQL itself to 'replicate' the whole database to the second set in 'real time' ..