Backing up and restoring databases

Even backup and restore can be done through Invoke-Sqlcmd. It is just a matter of passing the actual BACKUP and RESTORE command to Invoke-Sqlcmd. The following is an example:

Import-Module SQLPS -DisableNameChecking

#current server name
$servername = "ROGUE"    # or localhost
$database = "Chinook"

$query = @"
BACKUP DATABASE Chinook
TO DISK='Z:\Backups\Chinook.bak'
WITH
    FORMAT, 
    COMPRESSION
"@

#code below in one line
Invoke-Sqlcmd -ServerInstance $servername -Database $database -Query $query

Although this is possible, it would be more elegant to use the Backup-SqlDatabase and Restore-SqlDatabase cmdlets (which were discussed in Chapter 4, Basic SQL Server Administration) since these are already provided with ...

Get PowerShell for SQL Server Essentials now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.