Look, we need these things
The setup for sp_AllNightLog creates jobs for four separate activities
1 job to poll for new databases to back up (primary)
1 job to poll for new databases to restore (secondary)
10 jobs to poll for backups to take (primary)
10 jobs to poll for restores to… whatever (secondary)
And, as Mr. Swart notes: “In the land of Mordor where the shadows lie.”
The number of backup and restore jobs is configurable, but we estimate that having between 4 and 20 jobs should suffice for most RPO goals.
Economical
All jobs are attached to the same schedule, called ten_seconds. Why? Well, if a job fails completely, we want it to restart. Most jobs run in a constant loop (While @Option = 1, BEGIN…), with a fair amount of error handling. Total failures should be rare. Having them all use the same schedule was an early design choice.
Along the way, we perform various checks to make sure that our target database exists — right now that’s hardcoded to msdbCentral, which helps easily tie using this into sp_BlitzBackups to trend RPO and RTO across servers. Huzzah! We also check that Agent is running, you haven’t attempted to insert an absolutely abhorrent backup path, and some other niceties that people often overlook. If there’s anything you think we should have in here, let us know!
Configurable
We really wanted to surface most settings. There are some table names that aren’t negotiable. You can change RPO, RTO, Backup, and Restore paths easily. Either update the tables directly, or use the Setup proc with the @Update action.
We also want you to be prepared for what’s about to start happening on your server: Either a Whole Mess® of backups or restores. That’s why the Agent jobs aren’t activated from the get-go. You’ll have to do that when you know you’re ready.
Optimizations
We really only had to make one tweak to sp_DatabaseRestore, to take into account log backups that have already been restored.
Unfortunately, when you run a dir command via xp_cmdshell, there’s not a great way to filter files based on a date. That would have made the insert to table variable more efficient, sure. But we settled, and we just delete from the table variable before a certain date. This means that for a directory with thousands of log backups in it, you’re not restoring headers for all the ones you’ve done already to see if you need to do them again.
We hope you find these stored procs useful. If there’s anything we can do to make them more useful or applicable, head on over to our GitHub repo to let us know.
Thanks for reading!
