Jeff Moden - Wednesday, June 28, 2017 6:55 AM
Just a few months ago, I wrote a cursor for a SQL Agent job to take database backups. Yes, a cursor for backing up databases.
Our situation, inherited from corporate after they switched to deduplication technology for most of the servers, is that each server instance has its own folder on a locked snapshotted NAS where the folder structure consists of InstanceName -> DatabaseName -> DatedFolder (with _FULL on it if it's the day of a FULL backup, otherwise just the date). Differentials happen on the non-FULL backup days and transaction logs go in the dated folders.
The cursor also creates the db and dated folders if they don't already exist by the time the backup runs. It's actually pretty elegant all things considered. Especially as the native backup technology to SQL Server doesn't quite adhere to the folder structure mandated by corporate rules.