Carpe Datum

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

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()

$programSource = "Read XML File"

write-Host $programSource

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

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:

 </servers>