T-SQL To Check For Directories

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).

About these ads