The Challenge

Create a database called "Sandbox" on one of your instances. Set up a striped backup that creates a full backup of "Sandbox" using two files. Name them with the database name, Stripexx where xx is the number of the stripe, and with the date.

Setting Up a Striped Backup

Setting up a striped backup is simple, so simple in fact that many people have inadvertently done it. I'll walk through the process here.

I've got a database on my laptop called "Sandbox". I've got a script that runs backups automatically for me, and it stores those backups in the folder below. For this demonstration, I'm going to stripe a backup to two files and store them in the folder you see below, at the same level in the filesystem as the "Full" folder. As you can see, there are no files in there now.

To create the striped backup, I'll follow a similar process to that I would for a single file backup. Right click the database and select "Tasks" and then "Back Up", as shown below.

This brings up the backup dialog, which you can see below. Note that the previous backup file I used is stored in the Destination box at the bottom of the form. I don't want to use that file, as it contains another full backup. 

Here is the last file that I used, but this isn't the one I want to use. Clicking "Remove" takes this away. If you never remove these old files, regardless of what is highlighted in this dialog, you're creating striped backups. To avoid confusion here, always remove previous files.

Once I've done that, the dialog is empty, as shown below. I'll next click "Add".

Once I've clicked "Add", I get this form shown below. This is the "Select Backup Destination" where I can type something, or click the ellipsis to the right. I'll do that.

Here I have the bottom portion of the dialog that appears. I've browsed to the location in the first image above, and selected the "Sandbox" folder. We can see the "Full" folder below that. In the Filename box, I'll type the name of a backup file. 

I tend to use a format of databasename_datetime.bak for full backups. I use .trn and .dff for other backups out of habit and convention. However here, I've added a "_stripe1" in the middle. Why? Because I want to recognize this is a striped backup. I could add this at the end, and thinking about sorting files in the folder, that's probably a better convention. Even better would be "_stripe1_of_2" or something similar.

While you can query for some information from backup files, it's always nice to provide information to the human that might be stressed when they're performing a restore. Especially if that person is the one doing the work and you're trying to walk them through it on the phone.

I click OK a couple times and come back to the main Back Up Database dialog. I can see my file in the Destination edit box.

At this point, everything is the same as any other backup process.

Here's where we create the stripe. Click the "Add" button again, and then click the ellipsis. Browse to the location again and enter a different file name. Note I've done that below with the "_stripe2" in the filename. Click OK a few times.

At this point when you return to the main Back Up Database dialog, you should see this.

There are two files listed in the Destination. The backup will be striped across these two files, roughly half of the data in each file. It doesn't matter which file is highlighted above; both files will be used for the backup.

Click OK here. The backup will run and you should get a success message as shown below.

If I now check the folder in the filesystem, I see both files. Each file is about 160MB in size, which makes sense. My last full backup was about 3222MB in size.

You can also watch a video demonstration of a striped backup here.

Using T-SQL

Performing a striped backup from T-SQL is even easier. If I have this as a non-striped backup command:

BACKUP DATABASE Sandbox
 TO DISK = 'Sandbox_20150123.bak'

I can convert this to a striped backup by just adding another file, as shown below.

BACKUP DATABASE Sandbox
 TO DISK = 'Sandbox_20150123_stripe1_of_2.bak'
  , DISK = 'sandbox_20150123_stripe2_of_2.bak'

Note I've fixed my filenames here to a better pattern.

You can easily see this documented in the BACKUP command.