Archive for April, 2009

Backing up SQL Express Databases

SQL express is very useful for small installations, websites etc.  But more often than not I find it important to Backup the database on a schedule. This feature is supplied in the more advanced versions of SQL server but not in the express edition.

However, thanks to PowerShell it is now possible to provide the same functionality.  This is how I went about it.  Please note that I found most of the supporting functions online and they aren’t really mine.  Just the way I put them together.

First step.  We need to backup the database.

To back up the database you need to execute the following three SQL statements.

   2: NAME='$DbName-Full Database Backup'
   3: GO
   5: BACKUP LOG [$DbName] to DISK='$DbFile.TRN'
   6: GO
   9: GO

Well they aren’t true SQL statements as I have used a few variables in there, however that is enough for you to get the idea.

The first statement backs up the $DbName (powershell variable) to $DbFile.

The second statement backs up the log file.

And the third statement truncates the log file, which we just backed up.

Second step. Execute SQL using PowerShell

This is easier than it looks as PowerShell enables you to call any .NET component.

   1: function execute-Sql($server, $db, $sql )
   2: {
   3:   $Connstring = "server=$server;integrated security=TRUE;database=$db"
   5:   $sqlConnection = new-object System.Data.SqlClient.SqlConnection
   6:   $sqlConnection.ConnectionString = $Connstring 
   7:   $sqlConnection.Open()
   9:   $sqlCommand = new-object System.Data.SqlClient.SqlCommand
  10:   $sqlCommand.CommandTimeout = 120
  11:   $sqlCommand.Connection = $sqlConnection
  12:   $sqlCommand.CommandText= $sql
  13:   $text = $sql.Substring(0, 50)
  14:   Write-Progress -Activity "Executing SQL" -Status "Executing SQL => $text..."
  15:   Write-Host "Executing SQL => $text..."
  16:   $result = $sqlCommand.ExecuteNonQuery()
  17:   $sqlConnection.Close()
  18: }

Apart from a few text logging lines 13 through 15.  The rest is reasonably straight forward.  Create a connection to the $db on $server and execute $sql.

If you have a few databases you want to backup then calling this method could become rather tedious.  So lets wrap Step 1 and Step 2 together.

   1: Function BackupDB($Server, $DbName, $DbFile, $LogFileAlias)
   2: {
   3:   execute-sql $server $DbName "BACKUP DATABASE [$DbName] to DISK='$DbFile.BAK' WITH FORMAT, NAME='$DbName-Full Database Backup'"
   4:   execute-sql $server $DbName "BACKUP LOG [$DbName] to DISK='$DbFile.TRN'"
   5:   execute-sql $server $DbName "DBCC SHRINKFILE (N'$LogFileAlias', 0, TRUNCATEONLY)"
   6: }


Third step. Manage the Backup files.

Ok so now we can generate the SQL backup files and manage the database with a couple of lines.

   1: $curdate = Get-Date
   2: $formDate=$curdate.ToString("yyyyMMddhhmm")
   3: BackupDB  ".\SQLExpress" "Northwind" "c:\SQLBackup\Northwind-$formdate" "Northwind_Log"

These three lines will create a backup file and a Transaction log backup file of the form Northwind-yyyyMMddhhmm in the c:\SQLBackup folder.

If this is scheduled using a task scheduler we are going to have a lot of backup files build over time so we need one more helper function to manage these files.

   1: Function DelOldFile($Dir, $Pattern, $Days)
   2: {
   3:  if (Test-Path $Dir)
   4:  {
   5:   $Now = Get-Date
   6:   # Notice the minus sign before $days
   7:   $LastWrite = $Now.AddDays(-$days)
   8:   $Files = get-childitem "$Dir" -include "$Pattern" -recurse | Where {$_.LastWriteTime -le "$LastWrite"} 
  10:   foreach ($File in $Files)
  11:   #You can add -whatif to see the consequence – Remove-item $File -Whatif
  12:   {write-host "Deleting File $File" -foregroundcolor "Red"; Remove-Item $File |out-null}
  13:  }
  14:  Else
  15:  {Write-Host "The Folder $Dir Does Not Exist!"}
  16: }

Bingo… this function deletes any files under $Dir that match $Pattern and are older than $Days.

So after doing the backup we can call this function to remove any old backups that we don’t want anymore.


   1: DelOldFile "c:\SQLBackup" "Northwind*.BAK" 60
   2: DelOldFile "c:\SQLBackup" "Northwind*.TRN" 60


Fourth Step.  Schedule the Script.

This is easy.  Just setup a Windows Task to call powershell and execute the script. 

Ok… so to put it altogether you will need to paste the above script functions into a powershell script file, and do something so that the backup files end up on some other media than on the same disk spindle.  That defeats the purpose of a backup.  But that I’ll leave up to you to work out.

Categories: Programming