SQL端通过脚本判断文件夹是否存在 并创建

做数据库备份作业的时候..想按日期建立文件夹.. 

首先要判断文件夹是否存在

DECLARE @PATH VARCHAR(255)  --路径
DECLARE @DATE VARCHAR(255)  --日期 例如: 20171011
SET @DATE=CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)+CAST(DATEPART(MM,GETDATE()) AS VARCHAR)+CAST(DATEPART(DD,GETDATE()) AS VARCHAR)
SET @PATH = 'E:\SQL2012_BAK\'+@DATE+'\'
DECLARE @TEMP TABLE(A INT,B INT,C INT) --建立一个虚拟表 用来判断文件夹是否存在
INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @PATH
IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1) 
BEGIN
    --因为XP_CMDSHELL 函数的参数不允许使用变量拼接  所以使用EXEC(SQL)的方法
    DECLARE @EX NVARCHAR(255)
    SET @EX='EXEC XP_CMDSHELL ''MKDIR '+@PATH+''''
    EXEC(@EX)
END

如果报错

消息 15281，级别 16，状态 1，过程 xp_cmdshell，第 1 行
SQL Server 阻止了对组件“xp_cmdshell”的 过程“sys.xp_cmdshell”的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。

则使用以下修改配置项包裹以上脚本

--开启 XP_CMDSHELL 
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO


//===================

此处为使用了 XP_CMDSHELL 的脚本

//===================


--关闭 XP_CMDSHELL 
GO
SP_CONFIGURE 'XP_CMDSHELL',0
RECONFIGURE
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0
RECONFIGURE
GO

 

最后贴上数据库备份命令

--备份数据库
--此处可以放置多个数据库的备份 不必和定制作业步骤时选择的库是同一个
DECLARE @BAKFILE NVARCHAR(255)
SET @BAKFILE = @PATH+@DATE+'.DB1.BAK'
BACKUP DATABASE [DB1] TO DISK = @BAKFILE WITH COMPRESSION    
SET @BAKFILE = @PATH+@DATE+'.DB2.BAK'
BACKUP DATABASE [DB2] TO DISK = @BAKFILE WITH COMPRESSION

 

综上所述, 最终版的SQL备份命令是: (展开查看)

---------------------------
--开启 XP_CMDSHELL 
---------------------------
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO

---------------------------
--检查文件夹是否存在 不存在则创建
---------------------------
DECLARE @PATH VARCHAR(255)  --路径
DECLARE @DATE VARCHAR(255)  --日期 例如: 20171011
SET @DATE=CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)+CAST(DATEPART(MM,GETDATE()) AS VARCHAR)+CAST(DATEPART(DD,GETDATE()) AS VARCHAR)
SET @PATH = 'E:\SQL2012_BAK\'+@DATE+'\'
DECLARE @TEMP TABLE(A INT,B INT,C INT) --建立一个虚拟表 用来判断文件夹是否存在
INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @PATH
IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1) 
BEGIN
    --因为XP_CMDSHELL 函数的参数不允许使用变量拼接  所以使用EXEC(SQL)的方法
    DECLARE @EX NVARCHAR(255)
    SET @EX='EXEC XP_CMDSHELL ''MKDIR '+@PATH+''''
    EXEC(@EX)
END 


---------------------------
--备份数据库
---------------------------
DECLARE @BAKFILE NVARCHAR(255)
SET @BAKFILE = @PATH+@DATE+'.DB1.BAK'
BACKUP DATABASE [DB1] TO DISK = @BAKFILE WITH COMPRESSION    
SET @BAKFILE = @PATH+@DATE+'.DB2.BAK'
BACKUP DATABASE [DB2] TO DISK = @BAKFILE WITH COMPRESSION

---------------------------
--关闭 XP_CMDSHELL 
---------------------------
GO
SP_CONFIGURE 'XP_CMDSHELL',0
RECONFIGURE
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0
RECONFIGURE
GO