Creating a full backup

In this recipe, we will look at how we can do a full database backup using PowerShell.

Getting ready

We will use the AdventureWorks2008R2 database for this recipe. We will create a full compressed backup of the database to a timestamped .bak file in the C:\Backup folder. Feel free to use a database of your choice for this task.

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

BACKUP DATABASE [AdventureWorks2008R2] 
TO  DISK = N'C:\Backup\AdventureWorks2008R2_Full_20120227092409.bak'
WITH NOFORMAT, INIT,  
NAME = N'AdventureWorks2008R2 Full Backup', 
NOSKIP, 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 follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer...