Maint. Plan Backup Database Task - Across one or more files?

Question

Maint. Plan Backup Database Task - Across one or more files?

ZeeAtl

SSCommitted

Points: 1569
More actions
October 1, 2009 at 11:47 am

#136248

How do I setup 4 backup files <Base_DBName_1, ..._2, ..., _4> for each of my chosen databases that are backed up?
For example if I have databases:
SP_SC_1_Content
SP_SC_2_Content
SP_SC_3_Content
Now manually backing up these databases I create files:
SP_SC_1_Content_1.bak
SP_SC_1_Content_2.bak
SP_SC_1_Content_3.bak
SP_SC_1_Content_4.bak
SP_SC_2_Content_1.bak
SP_SC_2_Content_2.bak
SP_SC_2_Content_3.bak
SP_SC_2_Content_4.bak
SP_SC_3_Content_1.bak
SP_SC_3_Content_2.bak
SP_SC_3_Content_3.bak
SP_SC_3_Content_4.bak
Can I mimic this in the Maintenance Plan
Backup Database Task field Back up databases
across one or more files?
I would assume I'm forced to a set naming
convention with base filename being the Db-Name
and the sequence # of the file appended somewhere
in the filename. Correct assumption? The BOL is
pretty sparse on this selection.
Thanks,
Zee
SS DBA
Atlanta, GA

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

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

Swarndeep SSCrazy Points: 2347 More actions · Answer 1

If I have understood your query correctly, you are trying to create multiple backup files for each individual database.

You have to add one backup database task in maintenance plan for each database in order to create multiple files for each database. If you have two databases, you have to add two database backup tasks.

Swarndeep

http://talksql.blogspot.com

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

First, is there a particular reason for wanting to stripe your backups? Are you trying to reduce the amount of time it takes to perform the backups?

If so, have you looked at using one of the many tools available for backing up databases that are available instead of doing this yourself? Redgate has a backup utility, as well as Idera and Quest (we use Litespeed for some of our systems).

If you are looking at reducing storage requirements - then you can also look at HyperBac for compression, but you would still have to stripe the backups yourself.

With that said, instead of using the maintenance plan task, use an Execute SQL Task and generate the SQL yourself. This way, you have control over the naming convention - the number of files and how the code is actually generated.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Norberto Santin SSC Enthusiast Points: 109 More actions · Answer 3

I think that the requirement of doing striped files backups really improves the performance, I think we all agree on that, but it's also very important the housekeeping of the backup files (retention).

Regular backup to disk does not includes the houskeeping if you want for example 1 week retention.

And based on maintenance plans perfectly covers the housekeeping, think that the point of this post is to know if there's anyway to take the advantanges of both... which would be "A maintenance plan able to backup to multiple files"... is that possible?

ourehman SSC Veteran Points: 267 More actions · Answer 4

yes, it is possible. In the backup task you have the option to specify multiple files for the backup. You might want to choose a separate backup task for each database though.