Backup SQL Server by Using PowerShell Scripts

There are many ways to back up SQL server databases. Normally it is done through DBA to create a scheduled job on SQL Server. However, this requires a full version of SQL server. For Microsoft SQL Server 2005/2008 Express version, one of its limitation is that the free version does not provide job scheduling. During my past working experience, I fount several ways to the backup.

The basic requirements for the database backup job are:

• The job can be scheduled as an automation job without user interaction
• The job is preferred in script for each maintenance, for example, database, user/pwd, and backup location changes.
• The backup job is centralized on one place so that several SQL databases are backed up on one central location.

SQL Server Utility

The first tool I found is to use SQL management tool SQLMaint.exe. It comes with SQL Server Management Studio 2005. Here is the technical information of this tool. For example, I use the following batch commands to do a database backup for MyDatabase on SQL server PC001\sqlexpress:

@echo off
REM
REM This path is SQL binary folder for sql maintanance app
REM
pushd "C:\MSSQL2005\MSSQL.2\MSSQL\Binn"
sqlmaint.exe -S PC001\sqlexpress -U dbbackup -P pwd 
  -D myDatabase -CkDB -BkUpOnlyIfClean 
  -Rpt C:\MSSQL_Backup\Log\PC001MyDatabase_backup_log.txt 
  -VrfyBackup -BkUpMedia DISK 
  -BkUpDB C:\MSSQL_Backup\DB 
  -DelBkUps 3days -DelTxtRpt 3weeks 
  -HtmlRpt C:\MSSQL_Backup\HTMLRpt\PC001MyDatabase_backup_report.html
  -DelHtmlRpt -3weeks
popd
@echo on

The batch commands work fine with only SQL Server 2005 database; however, it does not work for SQL Server 2008 or Expression databases.

PowerShell Solution One

By googling web, quickly I found an alternative way to do the job. Those scripts are based on Microsoft.SqlServer.xxx classes. That's very cool! Based on those scripts and my requirement, I created a function. This function does database backup by either Windows log-on user credential, or SQL Server user credential. In either case, the credential user should be configured in the SQL server with db_backupoperator permission.

The function takes following parameters:

• SQL Server Name, for example, PC001\SQLEXPRESS
• database name, for example, myDatabase
• folder: a path on SQL server where the backup file will be saved
• SQL user name. This is optional. If it is not supplied, the current Windows log-on user's credential will be used
• password for the above SQL server user. Optional

Here is the script:

function BackupSQLDb (
  [string]$p_sqlServerName = ${throw "Missing sql server name "},
  [string]$p_db = ${throw "Missing parameter database name"},
  [string]$p_DestFolder = ${throw "Missing parameter destination folder"},
  [string]$p_userName,
  [string]$p_password
)
{
  #load assemblies
  #note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
  #otherwise may get this error
  #Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
  #the assembly containing this type is loaded.

  [System.Reflection.Assembly]::
    LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  #Need SmoExtended for smo.backup
  [System.Reflection.Assembly]::
    LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
  [System.Reflection.Assembly]::
    LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
  [System.Reflection.Assembly]::
    LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

  $sqlServername = $p_sqlServerName

  $sqlUserName = $p_userName
  $sqlPWD = $p_password
  #create a new server object
  $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")
    -ArgumentList $sqlServername # "PC001\sqlexpress"
  $backupDirectory = $p_DestFolder
  #display default backup directory
  Write-Debug ("Default Backup Directory: {0}" -f $backupDirectory)
  if ( $sqlUserName -ne $null -and $sqlUserName.length -gt 0 ) {
    $server.ConnectionContext.LoginSecure=$false;
    $server.ConnectionContext.set_Login($sqlUserName)
    $securePassword = ConvertTo-SecureString $sqlPWD -AsPlainText -Force
    $server.ConnectionContext.set_SecurePassword($securePassword)
  }

  $db = $server.Databases[$p_db]
  $dbName = $db.Name
  if ( $dbName.length -gt 0 )
  {
    $timestamp = Get-Date -format yyyyMMdd_HHmmss
    $backupFile = $backupDirectory + $dbName + "_" + $timestamp + ".bak"
    Write-Output ("Start backup database ""{0}"" to ""{1}"""
      -f $dbName, $backupFile)

    $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
    #BackupActionType specifies the type of backup.
    #Options are Database, Files, Log
    #This belongs in Microsoft.SqlServer.SmoExtended assembly
    $smoBackup.Action = "Database"
    $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
    $smoBackup.BackupSetName = $dbName + " Backup"
    $smoBackup.Database = $dbName
    $smoBackup.MediaDescription = "Disk"
    $smoBackup.Devices.AddDevice($backupFile, "File")
    $smoBackup.SqlBackup($server)
    Write-Output ("Finished backup database ""{0}"" to ""{1}"""
      -f $dbName, $backupFile)
  }
  else {
    Write-Output ("ERROR: invalid database name or database does not exist: {0}"
      -f $p_db)
  }
}

I only tested this function on Windows XP and Windows 2008 Server with SQL Server 2008 Express installed.

PowerShell Solution Two

The above script function works fine with SQL Server 2005 and 2008 and Express versions. However, it does not work for SQL Server 2000! I realized one day that there is option to obtain the backup TSQL scripts from SQL Server Management studio. I verified that in the TSQL command stays same in all SQL Server versions: 2000, 2005 and 2008. How about to make a connection to SQL server and run the TSQL command? Quickly I come to the solution two.

function BackupSQLDb (
  [string]$p_sqlServerName = ${throw "Missing sql server name "},
  [string]$p_db = ${throw "Missing parameter database name"},
  [string]$p_DestFolder = ${throw "Missing parameter destination folder"},
  [string]$p_userName,
  [string]$p_password
)
{
  $timestamp = Get-Date -format yyyyMMdd_HHmmss
  $backupFile = $p_DestFolder + $p_db + "_" + $timestamp + ".bak"
  $backupDescription = "Full backup of {0}" -f $p_db
  Write-Output ("Start backup database ""{0}"" on SQL Server({2}) to ""{1}"""
    -f $p_db, $backupFile, $p_sqlServerName)
  # TSQL command for backup
  $tsqlCmd = "BACKUP DATABASE {2} TO  DISK = N'{0}' WITH NOFORMAT, NOINIT,  NAME = N'{1}', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
    -f $backupFile, $backupDescription, $p_db
  $con = $null
  if ( $p_userName -ne $null -and $p_userName.length -gt 0 ) {
    # Use SQL user/password
    $con = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}"
      -f $p_sqlServerName, $p_db, $p_userName, $p_password
  }
  else {
    # Use Windows log on credential
    $con = "Data Source={0};Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1}"
      -f $p_sqlServerName, $p_db
  }
  Write-Output ("Connecting to {0} ..." -f $con)
  $cn = new-object System.Data.SqlClient.SqlConnection ($con)
  $cn.Open()
  $cmd2 = new-object "System.Data.SqlClient.SqlCommand" ($tsqlCmd, $cn)
  $result = $cmd2.ExecuteNonQuery()
  $cn.Close()
  Write-Output ("Backup database is done with result {0}" -f $result)
}

I did similar tests for this function. It seems that it works for all SQL Servers, 2000, 2005, and 2008.

References

SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell

SQL Server PowerShell : Basics – Connecting to SQL Server

JBs Powershell blog: SQL Queries.