SQL – Backup Database Schema and SQL Jobs via Powershell

Hi, being a developer at times we value our code more than the content that is modifies…

Hmmm.. Well sounds suspicious from the business standpoint, but from a developer perspective our code is gold !!!!

Any ways, this post outlines one of the methods to backup your MSSQL database schema and SQL jobs regularly.

This proved valuable to me many times, as MSSQL has slightly loose integration with versioning tools like GIT/SVN/TFS.

There are few tools out there in the market to bridge this gap but sadly none are free.

APEX SQL Server source control
Red Gate – SQL Source Control

With this post content I do not promise to provide any full fledged versioning tool for MSSQL, but a power shell script which to some extent when ran at regular intervals would greatly help you to achieve the necessary backups.

Database Scripter:

param(
[string] $folderPath,
[string] $serverName,
[string] $dbName
)

$Filepath= $folderPath #'C:\tmp\db_script' # local directory to save build-scripts to
$DataSource= $serverName #'SERVERNAME' # server name and instance
$Database= $dbName #'DBNAME'# the database to copy from
$dateStamp = $(get-date).ToString("yyyyMMdd");
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database] 
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$transfer.Options.ScriptBatchTerminator = $true # this only goes to the file
$transfer.Options.ToFileOnly = $true # this only goes to the file
$transfer.Options.ScriptData = $false
$transfer.Options.Filename = "$($FilePath)\$($Database)_$($dateStamp)_Build.sql"; 
$transfer.ScriptTransfer() 
"All done"

SQL Job Scripter:

Param(
[string]$server
, [string]$name
, [string]$path
)

$dateStamp = $(get-date).ToString("yyyyMMdd");
###  Note that the SMO library may be in a different directory, depending on your version of SQL Server
$smolibrary = "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$nl = [Environment]::NewLine
Add-Type -Path $smolibrary
$jobserver = New-Object Microsoft.SqlServer.Management.SMO.Server($server)

foreach ($job in $jobserver.JobServer.Jobs | Where-Object {$_.Name -like "$name*"})
{
    $newfile = $path + "\\$($dateStamp)_" +  $job.Name + ".sql"
    New-Item $newfile -ItemType file | Out-Null
    "USE msdb" + $nl + "GO" + $nl + $nl + $job.Script() | Out-file $newfile
}

Once the above scripts are on the server (Usually the MSSQL Box) you want to take backup you can then just create a job in the MSSQL with the following illustrated step, to regularly call the above scripts with the desired frequency.

This would allow to backup only the script/code part of the database, hence when you would like to refer back to an old archive (for your stored procedures, table definitions, views and similar) you are protected by not requiring the whole DB restore to retrieve one code component.

And this comes in real handy in case of large databases where restore takes ages.

The resultant backups are in the form as follows:

Database Scripts:

MSSQL Job Scripts:

Note: In order for this scripts to work you would need to install the “Shared Management Objects” on the box where this scripts need to execute as they use the “Microsoft.SqlServer.Smo.dll” component.

For MSSQL 2014 one can download from the following link:

Microsoft® SQL Server® 2014 Feature Pack

Hope it helps.