Backing up and restoring databases

Backing up and restoring can be accomplished using SQL Server Management Objects (SMO) or by using the cmdlets available in the SQLPS module. As there are cmdlets available, in this section, we will focus on how to use these cmdlets.

Backing up

The Backup-SqlDatabase cmdlet that comes with the SQLPS modules allows you to perform database backups using different options. When you run Get-Help Backup-SqlDatabase, you should get a full list of syntax and examples. The options you get with this cmdlet are similar to the options you have with the BACKUP DATABASE T-SQL command. The following is an example script that performs a full database backup on a timestamped backup file:

Import-Module SQLPS -DisableNameChecking

#current server name
$servername = "ROGUE"

#$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername

$dbname = "Chinook"
$currdate = Get-Date -Format yyyyMMddHHmmss
$backupfolder = "C:\BACKUP\"

#generate backup file path and...