SQL Express – How do I back this up?

An alarming number of third party applications come with an installation of SQL Express, and without any intention of it ever getting backed up.   I have been lucky to have never found this out the hard way, but I can envision many who have.   So what do we do about these SQL Express instances that have no SQL Agent and no simple way of backing them up?   I have a couple of approaches that we have used in the past and figured they are worth sharing.  If this does not help anyone, maybe it got you to go out and scan your LAN’s for SQL Express instances out there which would be a win on it’s own.

  • The SSIS Approach.

Assuming you have another server pretty much anywhere that has SQL Server Integration Services installed on it, you can create yourself a very simple package with very simple Execute SQL Task that runs the backup against the remote SQL Express instance.  You can follow this up with file deletion using either a Script Task to delete expired files or via the xp_delete_file command that Patrick Keisler talks about very nicely in his blog.  A simple approach and it is usually within most people’s basic tool set, since it is just using SSIS and a fairly simple package.

 

  • The Powershell Approach

With the Powershell approach, you need to have another SQL Server instance that is SQL 2008 or above (and not SQL Express), so that it can run a SQL Agent Powershell script task.  This approach allows you to specify the Server\Instance, Databasename, path you want to backup your DB to and the Path you would like to copy the file over to and because you should, it even includes running your CheckDB for you.

####set up parameters
$servername = "mysqlexpress\instancename"
$dbname = "YourDBHere"
$bkdir = "\\UNCPath\Share\Name\AndFolderHere\"
$copydir = "\\AnotherUNCPath\ToCopyTheFileTo\SoThatYouHaveRedundantBackups\"
$searchstr = "*DBName*"  
$searchext = "*.bak"
$dt = get-date -format yyyyMMddHHmmss
$bkfile = $bkdir +  $dbname + $dt + ".bak"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
 
# create source and destination sql servers
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
# set backup directory, dbname and grab date
#create backup object and set properties
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 0
$dbbk.BackupSetDescription = "Backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.Devices.AddDevice($bkfile, 2)
$dbbk.MediaDescription = "Disk"
$dbbk.Incremental = $false
 
#Backup the database
$dbbk.SqlBackup($s)
Copy-Item $bkfile $copydir -force

Invoke-SQLCMD -Query "DBCC CheckDB([$dbname]) with no_infomsgs" -ServerInstance $servername

Nothing earth-shattering here, but if you ever find that you have an extra SQL Express instance out there and it is not being backed up, either of these two approaches should get that job done for you.