Fun with Powershell, SQL Backup and Automated Restores

Posted by Dylan Beattie on 24 September 2010 • permalink

Our database backup system is based on taking a full backup of every database every night, and transaction log backups every 20 minutes. Backups are shipped offsite to a backup server sitting in a data-centre across town, and what I was trying to do was to automate the process of restoring the latest full backup and all subsequent transaction logs on the remote server.

So: ingredients. I have :

  • Red Gate SQL Backup (and the SqlBackupC.exe command-line client)
  • Windows Powershell
  • A list of databases in a text file (“databases.txt”)
  • A  folder full of database backups, and a folder full of transaction log backups.

The backup filenames look something like:

D:\backup\
  data\
    FULL_myserver_Animals_20100921_183021.sqb
    FULL_myserver_Animals_20100922_183021.sqb
    FULL_myserver_Animals_20100923_183021.sqb
    FULL_myserver_Northwind_20100921_183021.sqb
    FULL_myserver_Northwind_20100922_183021.sqb
    FULL_myserver_Northwind_20100923_183021.sqb
  logs\
    LOG_myserver_Animals_20100921_190002.sqb
    LOG_myserver_Animals_20100921_200004.sqb
    LOG_myserver_Animals_20100921_210003.sqb
    LOG_myserver_Animals_20100921_220002.sqb
    LOG_myserver_Animals_20100921_230005.sqb
    ...
    ...

What I’m trying to do is, for each backup listed in databases.txt, I want to find the most up-to-date full backup, restore it, and then restore, in chronological order, every transaction log that’s been created since that full backup was taken.

So. Powershell… time to see what all the fuss is about.

Let’s start with a couple of things that threw me until I got my head around them. Powershell is a shell. Don’t think of it like C# or Java – think of it like a batch file on steroids. With objects. If you want to run a program from a Powershell script, you just put its name in the script. For example – this is a valid Powershell script that’ll open itself in Notepad:

C:\Windows\System32\Notepad.exe myscript.ps1

Copy that line into notepad, save it as as myscript.ps1, fire up Powershell.exe, navigate to the folder where you saved it, and type .\myscript.ps1 – bingo.

Secondly, Powershell is designed so that if something has a certain meaning in DOS, it’ll mean the same thing in Powershell. <, | and > will redirect and pipe output – just like in DOS – which means Powershell can’t use > as the greater-than operator – so they’ve used –ge instead. (Mmm. Perly.) Backslashes are path separators – just like in DOS – so Powershell uses the backtick (`) as an escape character.

Thirdly, Powershell supports Perl-style string interpolation.

$foo = 10
echo “I have $foo fingers”

will output “I have 10 fingers”. To avoid this behaviour, use single-quotes instead:

$foo = 10
echo 'I have $foo fingers'

will print "I have $foo fingers” Be careful, though – underscores are valid in variable names, so

echo “LOG_$server_$catalog_$timestamp”

is actually going to try and find variables called $server_ , $catalog_ and $timestamp. To work around this behaviour, enclose the variable name in braces:

echo “LOG_${server}_${catalog}_${timestamp}”

Finally – Powershell supports aliases, which means most commands have more than one name. For example, there’s a built-in command Get-ChildItem – and dir, gci, and ls are all shortcuts for this command, so when you bring up a Powershell command and want to list the contents of the current directory, it doesn’t care whether you type dir, ls, gci, or Get-ChildItem – they all do exactly the same thing.

OK. Time for scripty fun. Here’s the script – you’ll need to supply your own databases.txt and credentials, and probably tweak the 

# Read the contents of databases.txt into a collection called $databases
$databases= Get-Content "databases.txt"

# Path to the SQL Backup command-line client.
$exe = "C:\Program Files (x86)\Red Gate\SQL Backup 6\SqlBackupC.exe"

# Credentials for connection to SQL Server. 
$username = 'sa'
$password = 'p@ssw0rd'
# Database backup encryption password (you *do* encrypt your backups, don’t you… ?)
$dbpw = 'T0P$3CR3T'
foreach($database in $databases) {

  # Get a collection of ALL backup files in the backup folder.
  $allSqbFiles = Get-ChildItem D:\backup\data\*.sqb 
  
  # Create a regular expression that'll match filenames
  # against the current database name
  $regex = "FULL_\(local\)_${database}_\d+_\d+\.sqb"
  
  # Filter the list of backup files to find those for the current DB
  $backups = $allSqbFiles | Where-Object { $_.Name -match $regex }
  
  # Sort the backups by LastWriteTime...
  $backups = $backups | Sort-Object LastWriteTime

  # and extract the most recent one
  $latestBackup = $backups | Select-Object -last 1

  # Capture the LastWriteTime of the most recent backup  
  $t = $latestBackup.LastWriteTime

  # Extract the full name of the database backup file  
  $backup = $latestBackup.FullName
  
  # Construct the SQL statement to pass to SqlBackupC.exe - note the 
  # doubled double-quotes used to include a double-quote in the result.
  
  $sql = """RESTORE DATABASE [$database] FROM DISK = '$backup' WITH PASSWORD = '$dbpw', NORECOVERY, REPLACE"""
  
  # This next bit is what actually does the database restore, and 
  # it's a bit fiddly.
  # & (the ampersand) is known as the "call" operator, and 
  # basically says "hey, run this command" Note that you CAN'T just
  # give it a big fat string containing the whole command, arguments
  # and everything. That's not how it works.
  # Second, the arguments beginning with a hyphen need to be
  # backtick-escaped so Powershell knows they're not operators.
  # Finally, note how we've included the $exe in quotes,
  # because the path to the SqlBackupC.exe has spaces in it. 

  & "$exe" `-U $username `-P $password `-SQL $sql
  
  # Use another regex to grab all the LOG files for the current database.
  $regex = "LOG_\(local\)_${database}_\d+_\d+\.sqb";
  $logFiles = Get-ChildItem D:\backup\logs\*.sqb
  $logFiles = $logFiles | Where-Object { $_.Name -match $regex }
  
  # Then we filter this list to return only those that are more
  # recent than the full backup (which we captured earlier)
  $logFilesToRestore = $logFiles | Where-Object {$_.CreationTime -ge $t } | Sort-Object CreationTime
  
  $logFileCount = $logFilesToRestore.Length
  
  # If there's no log files, we break out of the loop and move
  # onto the next database.
  if ($logFileCount -le 0) { continue }
  
  # Now, the LAST transaction log needs a slightly different SQL 
  # command, because we want the last restore to leave the database
  # in a usable state, so we need to split the logs into the final
  # one, and all the others. 
  $splitAtIndex = $logFileCount - 2
  
  # Powershell lets you slice arrays by saying $MyArray[x..y]
  $logFilesExceptFinaltOne = $logFilesToRestore[0..$splitAtIndex]
  
  $finalFogFile = $logFilesToRestore | Select-Object -last 1
  
  foreach($log in $logFilesToRestore) {
    $logFileName = $log.FullName
  
    # Construct the SQL statement to restore the transaction log
    # leaving the database ready to accept further log restores:
    $sql = """ RESTORE LOG [${database}] FROM DISK = '${logFileName}' WITH PASSWORD = '${dbpw}', NORECOVERY"" "
   
    # and run SqlBackupC with that SQL statement:
    & "$exe" `-U $username `-P $password `-SQL $sql
  }
   
  $logFileName = $finalFogFile.FullName
   
  # Construct the SQL statement to restore the last 
  # transaction log and leave everything ready for use
  $sql = """RESTORE LOG [${database}] FROM DISK = '$logFileName' WITH PASSWORD = '${dbpw}',
            RECOVERY, ORPHAN_CHECK "" "
   
   # and run it!
   & "$exe" `-U $username `-P $password `-SQL $sql
 }

Having never really used Powershell before, this whole thing took about three hours to put together – and I spent the first two hours cursing Powershell for being so damned awkward, and then it just clicked and suddenly everything made a lot more sense. Hopefully the tips above will save you a bit of frustration if you’re taking your first steps with it… and I’ve a feeling I’m going to be doing a lot more Powershelling before too long. For stuff like this, it’s so much more powerful than batch files, and so much more maintainable than writing little command-line utilities in C#.