Creating a backup on mirrored media sets

In this recipe, we will create a full database backup on mirrored backup files.

Getting ready

We will use the AdventureWorks2008R2 database for this recipe. We will create a mirrored backup of the database, and both timestamped backup files will be stored in C:\Backup. Feel free to substitute this with the database you want to use with mirrored backups.

The T-SQL syntax that will be generated by this PowerShell recipe will look similar to:

BACKUP DATABASE [AdventureWorks2008R2] 
TO  DISK = N'AdventureWorks2008R2.bak' 
MIRROR 
TO  DISK = N'C:\Backup\AdventureWorks2008R2_Full_20120227092409_Copy1.bak' 
MIRROR TO  DISK = N'C:\Backup\AdventureWorks2008R2_Full_20120227092409_Copy2.bak' 
WITH FORMAT, INIT,  
NAME = N'AdventureWorks2008R2 Full Backup', SKIP, REWIND, 
NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module as...