Creating a full backup

In this recipe, we will see how to create a full database backup using PowerShell.

Getting ready

Using the AdventureWorks2014 database, we will create a full, compressed backup of the database to a timestamped backup (.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 the following:

BACKUP DATABASE [AdventureWorks2014]
TO  DISK = N'C:\Backup\AdventureWorks2014_Full_20150314092409.bak'
WITH NOFORMAT, INIT,
NAME = N'AdventureWorks2014 Full Backup',
NOSKIP, REWIND, NOUNLOAD, COMPRESSION,
STATS = 10, CHECKSUM

How to do it...

The steps to create a full database backup are as follows:

1. Open PowerShell ISE as administrator.

2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $instanceName = "localhost"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server...