SQL Server: Backup/Restore sample PowerShell script

This is a sample script that demonstrates a typical recovery scenario in SQL Server: full, differential and log backups restored in that order to bring back a database online.

(A pre-requisite to run it is to have SQL Server snap-ins added to PowerShell. See here.)

### SCRIPT STARTS HERE
$instance='.\SQLEXPRESS'

# drop db
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;IF DB_ID('DUMMY') IS NOT NULL ALTER DATABASE DUMMY SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;IF DB_ID('DUMMY') IS NOT NULL DROP DATABASE DUMMY"

# create db with a table
$filesDir="c:\Garbage"
if(-not(test-path $filesDir)){mkdir $filesDir}
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;CREATE DATABASE DUMMY ON (NAME = DUMMY_DATA,FILENAME='$filesDir\DUMMY_DATA.MDF') LOG ON (NAME = DUMMY_LOG, FILENAME = '$filesDir\DUMMY_LOG.LDF')"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;ALTER DATABASE DUMMY SET RECOVERY FULL"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;CREATE TABLE DATA(VALUE SMALLINT)"

# query db (and ignore error)
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;SELECT VALUE FROM DATA" -EA "SILENTLYCONTINUE"

# insert some rows
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;INSERT INTO DATA(VALUE) VALUES(1)"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;INSERT INTO DATA(VALUE) VALUES(2)"

# query db
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;SELECT VALUE FROM DATA"

# full backup
$full="$filesDir\Full.bak"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;BACKUP DATABASE DUMMY TO DISK='$full'"

# PROBLEM HERE, SOMEONE MISTAKENLY DROPS DATABASE
# drop db
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;ALTER DATABASE DUMMY SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;DROP DATABASE DUMMY"

# TEST: restore full backup and verify data is there
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;RESTORE DATABASE DUMMY FROM DISK='$full'"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;SELECT VALUE FROM DATA" -EA "SILENTLYCONTINUE"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;SELECT VALUE FROM DATA"

# insert a row
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;INSERT INTO DATA(VALUE) VALUES(3)"

# backup a transaction log
$diff="$filesDir\Differential.bak"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;BACKUP DATABASE DUMMY TO DISK='$DIFF' WITH DIFFERENTIAL"

# insert another row
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;INSERT INTO DATA(VALUE) VALUES(4)"

# backup another transaction log
$log="C:\Garbage\Log.bak"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;BACKUP LOG DUMMY TO DISK='$log'"

# drop database
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;ALTER DATABASE DUMMY SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;DROP DATABASE DUMMY"

# verify no data files are in place
dir $filesDir\*.mdf;dir $filesDir\*.ldf

# TEST: restore full backup plus the transactions logs and verify data is there
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;RESTORE DATABASE DUMMY FROM DISK='$full' WITH NORECOVERY"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;RESTORE DATABASE DUMMY FROM DISK='$diff' WITH NORECOVERY"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE MASTER;RESTORE LOG DUMMY FROM DISK='$log'"

# query db, we should have now 4 rows!
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;SELECT VALUE FROM DATA" -EA "SILENTLYCONTINUE"
Invoke-Sqlcmd -ServerInstance $instance -Query "USE DUMMY;SELECT VALUE FROM DATA"

# and we should have data and log files back
dir $filesDir\*.mdf;dir $filesDir\*.ldf

### SCRIPT ENDS HERE