Quick and Dirty: Backup SQL Server Express Instances on Enterprise Networks

I know the ideal Enterprise network won’t have any SQL Server Express instances, but every large network that I’ve ever worked on has at least a few. Many times, it’s powering apps like SolarWinds or BackupExec. Since SQL Express doesn’t run SQL Agent, it can be a pain to backup and maintain the systems.

There are a lot of blog posts out there that use PowerShell or some other scheduled script to perform the backups, but it occurred to me the other day that I can totally run all of my backup and maintenance scripts from another SQL Server’s SQL Agent. Duh! The key is to use the Connections tab to connect to other SQL instances.

I’ll be honest, because these servers may come and go and their databases are unpredictable, I don’t want to install anything local like Ola Hallengren’s Backup and Maintenance Scripts, so I setup simple Maintenance Plans with the following parameters:

  • I made one plan with multiple subplans.
  • Weekly: Full backup, integrity checks, index rebuild, Maintenance cleanup task (4 weeks), History cleanup task (4 weeks)
  • Daily: Differential, Maintenance cleanup task (4 weeks)
  • Staggered schedules between server backups — I don’t expect SQL Express databases to take any longer than 30 minutes, so every instance backup is scheduled 30 minutes apart.
  • The backups go to the same network share as the rest of the SQL instances which kinda look like this: \\sqlbackupserver\backups\HOSTNAME\full, \\sqlbackupserver\backups\HOSTNAME\diff.
  • No transaction log backups are scheduled — so far, I haven’t found a SQL Express instance with databases that are set to anything but the SIMPLE recovery model.

Make sure the SQL Server Agent account has adequate access to the SQL Server Express, and that you don’t check “Compress backups.” Otherwise, your backups will fail with a Backup Error: 3041, Severity: 16, State: 1 error code as SQL Express doesn’t support compressed backups.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*