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:


Script:


 # 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


$sqlConnection.Open()


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


$sqlCommand.Connection=$sqlConnection


$sqlCommand.CommandText= $sqlCommandVariable


$sqlCommand.ExecuteNonQuery()


}


# Main script Block


cls


# 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:



# XML Driver File:


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


 <servers>


 <server servername = “serverOne”>


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


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


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


 </server>


 <server servername = “serverTwo”>


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


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


 </server>


 </servers>