We help IT Professionals succeed at work.
Get Started

SQL Backup Job to Multiple Files in Unique folders per Database

Last Modified: 2012-05-12
When you use the GUI to create a SQL DB Backup job you can choose to backup to multiple files or you can choose to backup to a specific folder. If you choose the latter then you can also choose to create the backups in a separate folder for each database. So there'd be a folder called dbname1 with dbname1's backup(s) in it, and one for dbname2, etc.

However I'd like to do both. We found a sweet spot in that our backups run 40% faster if we back up to 3 files at once. However we have multiple databases so we'd like to do each to it's own folder. One top of that, if you choose to a folder, it puts a date line at the end of each file name so that you can have full backups for each day. However if you choose one or more files you are choosing the filenames yourself and thus it appears you can only have those files. Not previous days. You can append or overwrite.

Anyone know how to split it into multiple files (3 in this case) and then have multiple days back?

I'd rather not have to have separate maintenance plans or jobs depending on the day of the week, etc. It gets very complicated then once you have multiple databases, as we do.

Watch Question
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
This problem has been solved!
Unlock 1 Answer and 15 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE