Book Image

PowerShell for SQL Server Essentials

By : Donabel Santos
Book Image

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Table of Contents (15 chapters)
PowerShell for SQL Server Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Implementing Reusability with Functions and Modules
Index

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 the SQLPS module.