sql - 如果子目录不存在,则使用 SQL 脚本创建子目录
问题描述
下面我有一个测试脚本来备份未排除的数据库。我需要先检查这些目录是否存在于此路径中,如果它们不存在则创建它们。然后备份路径需要将每个文件备份到其各自的目录中。我对如何做到这一点有点迷茫,而且根据我们的环境要求,我不能使用 SSMS 维护计划来完成这项工作。任何建议表示赞赏。
谢谢
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @Datapath varchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
-- specify database backup directory
SET @path = '\\nas01\Admin\bbelden\test\'
SET @Datapath = '\\nas01\Admin\bbelden\test\' + @name
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @Path
IF NOT EXISTS (SELECT * FROM @DirTree WHERE subdirectory = @Name)
EXEC master.dbo.xp_create_subdir @DataPath
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master',
'model',
'msdb',
'tempdb',
'PracticaSd-Dallasmetro') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
RESTORE VERIFYONLY FROM DISK = @FileName
解决方案
这是答案部分
创建一个已经存在的目录不会出错或删除文件,因此请继续在每个循环中创建它。你不会丢失任何备份,都很高兴。
declare
@dirPath varchar(1024)
,@msg varchar(1024)
,@bkUp varchar(1024)
,@fileDate VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),112)
,@name VARCHAR(128) -- database name 128 is max for sql objects
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master',
'model',
'msdb',
'tempdb',
'PracticaSd-Dallasmetro') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @dirPath = '\\nas01\Admin\bbelden\test\' + @name
--set @dirPath = 'D:\SQL Server\MSSQL\Backup\' + @name --my home path
begin try
exec xp_create_subdir @dirPath;
set @msg = 'Created directory: ' + @dirPath
raiserror(@msg,0,0);
set @bkUp = 'backup database ' + quotename(@name) + ' to disk = ''' + @dirPath + '\' + @name + '_' + @fileDate + '.bak'' with compression';
exec(@bkUp);
set @bkUp = 'RESTORE VERIFYONLY FROM DISK = ''' + @dirPath + '\' + @name + '_' + @fileDate + '.bak''';
exec(@bkUp);
end try
begin catch
set @msg = 'something went wrong!!! with: ' + @name + ' ' + error_message()
raiserror(@msg,0,0);
end catch
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
这更像是带有一些脚本的评论。
此外,请注意尝试访问/创建文件或文件夹的用户是服务帐户。
检查/验证你可以
sp_configure 'advanced',1;
reconfigure;
GO
sp_configure 'xp_cmdshell', 1;
reconfigure;
GO
xp_cmdshell 'echo %USERDOMAIN%\%USERNAME%';
和前面的第一条评论一样,文件系统操作有许多未记录的存储过程。
- xp_delete_file
- xp_dirtree
- xp_file 存在
- xp_fixeddrives
- xp_subdirs
- xp_create_subdir