Carry on
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.
Prince Poll
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.
Poll Exemplar
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.
|
1 2 3 |
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.
|
1 2 3 4 |
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 NOT EXISTS.
Road trip!
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!
11 Comments. Leave new
Why not use master..xp_fileexist to check for dir exists instead of xp_cmdshell?
xp_fileexist only solves a small part of the problem.
After looking into your code, it does make sense to use xp_cmdshell to really scan the directory to determine which backups truly exist. Nice implementation.
Thanks! Yeah, it’s weird that there’s not a safer way to do this stuff. I have a blog post coming up about a new function in 2017 that helps, but isn’t a full replacement. Grumpiness abound.
You can use OLE Automation (https://www.dirceuresende.com/blog/operacoes-com-arquivos-utilizando-ole-automation-no-sql-server/) or CLR (https://www.dirceuresende.com/blog/sql-server-como-listar-ler-escrever-copiar-excluir-e-mover-arquivos-com-o-clr-c/) too. I think that is a better solution than xp_cmdshell.
Those are often tougher to get folks to enable though.
Just trade enabling CLR with disabling xp_cmdshell and make a CLR TVF called FileDir to make that kind of script very easy.
No more parsing xp_cmdshell string to find out the path, extension, size or date, just a simple select with a where clause.
Oli – most shops won’t enable CLR.
Oli — good news. It’s open source, so if you want to contribute the code as an alternative path, go ahead and hit up our GitHub repo: firstresponderkit.org
The xp_cmdshell parts would have to stay in for folks not willing to use CLR, though.
I also thought about OLE Automation, but I had very little consistent success with it…
Phew, it’s nice to see that someone else isn’t (toooooooo) iffy about using xp_cmdshell for this type of work. When used in the right way it’s a powerful companion. I built a very similar SP a few years ago that does just this thing (did I share the code though? No. Why? Coz you’re a jerk Browny that’s why…).
I digress. Backups were successfully being executed and I was getting the “Backup complete and verified” emails on a regular basis. What I didn’t see happening was that these backups were being removed by a separate process that was running on another machine elsewhere. Oops. So as a 2nd step to the file exists checking routine I also took advantage of robocopy within the OS to move the backup to a secure remote file share just after the verification took place. Stress levels reduced.