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.