I needed to use T-SQL to check for backup folders and create them if missing. Yes, I would normally use Powershell, but I’m saving up a rant for later on that topic…
This script by Jugal Shah worked quite well. Combined with Aaron Bertrand’s sp_ForEachDb, you can easily find and create missing backup directories. For example, suppose that you keep your backups at D:\Backups\[database name]\. After creating a new database called DB, there is no folder under D:\Backups\ named DB. If we tried to back up to that directory, we would get an error. Before running backups, you can run this code to generate folders given a particular structure.
declare @BackupDirectoryPath nvarchar(255) = 'D:\Backups\?'; declare @DatabaseList nvarchar(max); select @DatabaseList = STUFF((select ', ' + name from sys.databases where state_desc = 'ONLINE' and source_database_id IS NULL and name <> 'tempdb' FOR XML PATH ('')), 1, 1, ''); declare @sql nvarchar(max) = N' declare @file_results table ( file_exists int, file_is_a_directory int, parent_directory_exists int ); insert into @file_results ( file_exists, file_is_a_directory, parent_directory_exists ) exec master.dbo.xp_fileexist ''' + @BackupDirectoryPath + N'''; if exists (select * from @file_results where file_exists = 0 AND file_is_a_directory = 0) begin EXECUTE master.dbo.xp_create_subdir ''' + @BackupDirectoryPath + N'''; end '; exec dbo.sp_ForEachDb @command = @sql, @suppress_quotename = 1, @database_list = @DatabaseList, @print_command_only = 0;
Note that the question mark in the code will be substituted with the database name. Also note that because this is for backups, I wanted to get online databases (can’t back up offline datbases) which are not snapshots (can’t back those up) and are not tempdb (can’t back that up).