Enhancing Your Troubleshooting Toolset with PowerShell - SQL Server 2012 Internals and Troubleshooting - page 392

performance perspective are for backup and restore. SQL Server Backup and Restore cmdlets are a
welcome addition to SQL Server 2012; previously, as a script author you had two options:

➤

Invoke-SQL and write T-SQL that you execute from a PowerShell script.

➤

Load the SMO objects and use the backup and restore functionality provided through the
SMO library. This was my preferred approach but it requires a signii cant scripting effort,
somewhere in the neighborhood of 20-50 lines of script to handle a typical backup or
restore scenario.

Backup-SqlDatabase
The following script shows how simple database backups can be using the SQL Provider and the
new Backup-SqlDatabase script to iterate through all the databases, creating a folder for each one
and then generating a backup based on a timestamp (code i le: PS_SQLBackup01.PS1 ):

foreach($database in (Get-ChildItem))
{
$dbName = $database.Name
$timeStamp = Get-Date -FORMAT yyyyMMddHHmmss
$backupFolder = "c:\backups\$dbName"

if((Test-Path $backupFolder) -eq $False)
{
New-Item -type directory -path $backupFolder
}

Backup-SqlDatabase -Database $dbName -BackupFile "$backupFolder\$dbName-
$timeStamp.bak"
}

Restore-SqlDatabase
The new cmdlets also make restoring a database very straightforward. The following script demon-
strates how to restore the Adventure Works database:

Restore-sqldatabase -Database AdventureWorks '
-BackupFile "C:\Backups\AdventureWorks\AdventureWorks-20120220005537.bak"

You may notice the “`” character after AdventureWorks. This is not a mistake or a typo. It is a
continuation character; which means that you can type a line in the PowerShell command line, press
return, and PowerShell will continue accepting input for the previous line. This is very useful when
single statements become too long to read comfortably.

More than 40 parameters are available for Restore-SqlDatabase , enabling the creation of some
very complex restore scripting. Anything that is possible within the SQL Server Management
Studio user interface or through T-SQL is available here, with the added benei t of simple i le
management. This makes complex restores using multiple i les, such as restoring many log i les, a
relative breeze.