sample Powershell scripts for backup and restore sql server databases

backup script

Push-Location;Import-Module sqlps -DisableNameChecking;Pop-Location
$serverPath = "SQLSERVER:\SQL\mycomputer\SQL2012"
$databaseName = "payroll"
$backupTo = join-path (Get-Location) "$databaseName.bak"
Backup-SqlDatabase -Path $serverPath -Database $databaseName -Backupfile $backupTo

restore script

Push-Location;Import-Module sqlps -DisableNameChecking;Pop-Location
$serverPath = "SQLSERVER:\SQL\mycomputer\SQL2012"
$databaseName = "payroll"
$restoreFrom = join-path (Get-Location) "$databaseName.bak"
$databasePath = join-path $serverPath "Databases\$databaseName"
if(Test-Path $databasePath)
{
    Push-Location
    Set-Location $serverPath
    Invoke-SqlCmd "USE [master]; ALTER DATABASE [$databaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$databaseName]"
    Pop-Location
}
Restore-SqlDatabase -Path $serverPath -Database $databaseName -Backupfile $restoreFrom

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s