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

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

  • Comments 1

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> 

 

Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post