How do you split backup using SSMS and get date added to multiple files?

Question

How do you split backup using SSMS and get date added to multiple files?

WebTechie

SSChampion

Points: 11405
More actions
December 31, 2009 at 5:52 am

#217929

Hello,
I have a large database that I need to backup on a regular basis. I have the TSQL to backup the database to multiple files using query analyzer.
BACKUP DATABASE [MYDATA] TO
DISK = '\\MYSERVER\-- BAK\MYDATA20091231_1.bak',
DISK = '\\MYSERVER\-- BAK\MYDATA20091231_2.bak',
DISK = '\\MYSERVER\-- BAK\MYDATA20091231_3.bak',
DISK = '\\MYSERVER\-- BAK\MYDATA20091231_4.bak'
WITH INIT , NOUNLOAD , NAME = 'MYDATA_20091231', NOSKIP , STATS = 10, NOFORMAT
However, I would like to create a maintenance plan to run this. I'm trying to create the backup database task. If I backup to a file, that works fine. The backup database task will add to the TSQL the date.
When I add devices for multiple files, it doesn't automatically add the date. I added four devices:
\\MYSERVER\-- BAK\MYDATA_1.bak'
\\MYSERVER\-- BAK\MYDATA_2.bak'
\\MYSERVER\-- BAK\MYDATA_3.bak'
\\MYSERVER\-- BAK\MYDATA_4.bak'
How do I get the date added in SSMS' TSQL for multiple files?
This is a new one for me so, it may so easy and I'm just overlooking something. But any help in pointing me in the right direction is appreciated.
Thanks.
Things will work out. Get back up, change some parameters and recode.

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

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

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

You cannot use the standard backup database task provided by Microsoft to do what you want. You are going to have to create a script that does this for you and execute the script. You can place that script in an Execute SQL Task in the maintenance plan.

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

Chuck Lathrope Mr or Mrs. 500 Points: 568 More actions · Answer 2

You could use my backup script, or look at the code on how I did it. http://www.sqlwebpedia.com/content/ultimate-ms-sql-backup-script

-Chuck

@SQLGuyChuck

WebTechie SSChampion Points: 11405 More actions · Answer 3

Awesome!!

That is what I needed to know. I have a script already.

Thanks.

Things will work out. Get back up, change some parameters and recode.

WebTechie SSChampion Points: 11405 More actions · Answer 4

Chuck,

That looks like an awesome script. I need to study it some more.

Thanks.

Tony

Things will work out. Get back up, change some parameters and recode.