It turns out that the only thing harder than checking for new databases restored to a SQL Server, is checking a folder for a backup of a database that doesn’t exist on another SQL Server.
These are both part of what sp_AllNightLog has to do.
The other components, which use workers and a queue to backup and restore databases, were fairly easy to write. The important code had already been written.
Don’t thank me.
Seriously, don’t. I didn’t write any of that.
Polling on the “log shipping primary” basically just looks for new databases in sys.databases, and dumps them into our queue table to work on. This is easy enough.
On the “log shipping secondary”, we had to do some ballet.
Yes, it involves xp_cmdshell. If you’re horrified by this, feel free to stop reading and resume telling people that the sky is falling.
We do some checking to make sure that the path exists, and isn’t empty.
When we find folders for the first time, we have to restore the initial full backup. We insert some special canary values so our restore workers know this is the case.
After that, we just keep piling on log backups. This is the right thing to do.
This is the part of the code that calls sp_DatabaseRestore — and believe me, I’m so glad I didn’t have to write any of this.
Some of the ‘interesting’ parts of the restore polling code are in here. The backup polling code is literally just an intermittent check on sys.databases. If that makes your pants move, you can look at the code on your own time, creep.
First, we grab the path from a configuration table that gets populated when you run setup. Don’t worry if you mess this up, you can update it.
SELECT @restore_path_base = CONVERT(NVARCHAR(512), configuration_setting)
FROM msdb.dbo.restore_configuration c
WHERE configuration_name = N'log restore path';
Assuming that all works out, we set up our xp_cmdshell command, and insert that to a table variable.
I know, I know. but they kinda deserve each other.
SET @cmd = N'DIR /b "' + @restore_path_base + N'"';
INSERT INTO @FileList (BackupFile)
EXEC master.sys.xp_cmdshell @cmd;
It’s important to use
/b in the
dir command, so you don’t end up with an unparseable blob of directory information.
We also do some path checking here to make sure to make sure something usable comes back. The results when that’s not the case are pretty specific — there’s one NULL row, or you get a message like ‘The system cannot find the path specified’ or ‘File Not Found’.
Then we take the folder list from our table variable, and insert anything into the restore worker table that isn’t there already. Ah, the joys of
Down the line, I do need to add a slightly more recursive check to make sure any ‘new’ database folder has a subdirectory called FULL in it, and perhaps a bit to delete rows from the worker table if restore attempts come up empty. We’ll see how much of a problem certain scenarios turn out to be.
Thanks for reading!