SQL Server 2008 Improvements Practicals – PowerShell – Run a "Panic" Backup

I gave a presentation at TechEd 2008 where I covered our new features in “A Day in the Life of a DBA”. Here’s more of what I showed for 10:00am – “Panic Button”:

Now that I’ve found there are a few databases that haven’t been backed up, I want to run a “Panic Backup” on all of them. Here’s a PowerShell script, driven by an XML document containing the server names and database names that will take care of that for me – note that you could add a “WITH COPY” option so as not to break the Backup Chain:


 # Buck Woody

# Last changed: 05/16/2008

# Requires an XML file called “ServerList.XML”

# in the c:\temp\ directory

# SQL Server Access

Function sqlServerAccess($sqlServerVariable, $sqlDatabaseVariable, $sqlCommandVariable)


$programSource = “SQL Server Access for server: “ + $sqlServerVariable + “Database: “ + $sqlDatabaseVariable + “´rCommand: “ + $sqlCommandVariable

write-Host $programSource

# Connect and run a command using SQL Native Client, No return

$sqlConnection = new-object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = “server=” + $sqlServerVariable + “;integrated security=true;database=” + $sqlDatabaseVariable


$sqlCommand = new-object System.Data.SqlClient.SqlCommand


$sqlCommand.CommandText= $sqlCommandVariable



# Main script Block


# Create an XML document object, read in the file

$programSource = “Read XML File”

write-Host $programSource

$doc = [xml]( Get-Content c:\temp\PanicBackup.XML )

# Fist loop – used for the servers

foreach ($server in $doc.servers.server)


$programSource = “Server Connection for server “ + $doc.servers.server

write-Host $programSource

# Second loop – used for the databases

foreach ($database in $server.database)


$programSource = “Database Backup”

write-Host $programSource

$sqlCommandVariable=“BACKUP DATABASE “ + $database.databasename

$sqlCommandVariable=$sqlCommandVariable + ” TO DISK = ‘c:\temp\” + $database.databasename + “.BAK’ WITH COMPRESSION”

sqlServerAccess $server.servername “master” $sqlCommandVariable





# XML Driver File:

 <?xml version=”1.0″ encoding=”utf-8″?>


 <server servername = “serverOne”>

 <database databasename = “databaseOne”></database>

 <database databasename = “databaseTwo”></database>

 <database databasename = “databaseThree”></database>


 <server servername = “serverTwo”>

 <database databasename = “databaseOne”></database>

 <database databasename = “databaseTwo”></database>