The SQL side uses the script to determine if the folder exists and creates

When doing a database backup job: You want to create a folder by date ...

The first thing to tell is whether the folder exists

DECLARE @PATH VARCHAR(255)--PathDECLARE @DATE VARCHAR(255)--date for example: 20171011SET @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(AINTBINTCINT)--Create a virtual table to determine if a folder existsINSERT @TEMP EXEC [MASTER].. Xp_fileexist@PATHIF  not EXISTS(SELECT *  from @TEMP WHEREB=1) BEGIN    --The exec (SQL) method is used because the parameters of the xp_cmdshell function do not allow the use of variable stitching    DECLARE @EX NVARCHAR(255)    SET @EX='EXEC xp_cmdshell"'MKDIR'+@PATH+" '"    EXEC(@EX)END

If an error

15281  - 1 1 row SQL Server blocked access to the process "Sys.xp_cmdshell" of the component "xp_cmdshell" because this component was shut down as part of this server's security configuration. 

Use the following modification configuration item to wrap the script above

--Open xp_cmdshellGOsp_configure'SHOW Advanced OPTIONS',1RECONFIGUREGOsp_configure'xp_cmdshell',1RECONFIGUREGO//===================here is a script that uses xp_cmdshell//===================--Close xp_cmdshellGOsp_configure'xp_cmdshell',0RECONFIGUREGOsp_configure'SHOW Advanced OPTIONS',0RECONFIGUREGO

Finally paste the database backup command

--backing up the database--Here you can place backups of multiple databases without having to select the same library as the custom job stepDECLARE @BAKFILE NVARCHAR(255)SET @BAKFILE = @PATH+@DATE+'. DB1. BAK'BACKUP DATABASE [DB1]  to DISK = @BAKFILE  withCOMPRESSIONSET @BAKFILE = @PATH+@DATE+'. DB2. BAK'BACKUP DATABASE [DB2]  to DISK = @BAKFILE  withCOMPRESSION

In summary, the final version of the SQL BACKUP command is: (expand View)

-----------------------------Open xp_cmdshell---------------------------GOsp_configure'SHOW Advanced OPTIONS',1RECONFIGUREGOsp_configure'xp_cmdshell',1RECONFIGUREGO-----------------------------checks if a folder exists or does not exist then creates---------------------------DECLARE @PATH VARCHAR(255)--PathDECLARE @DATE VARCHAR(255)--date for example: 20171011SET @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(AINTBINTCINT)--Create a virtual table to determine if a folder existsINSERT @TEMP EXEC [MASTER].. Xp_fileexist@PATHIF  not EXISTS(SELECT *  from @TEMP WHEREB=1) BEGIN    --The exec (SQL) method is used because the parameters of the xp_cmdshell function do not allow the use of variable stitching    DECLARE @EX NVARCHAR(255)    SET @EX='EXEC xp_cmdshell"'MKDIR'+@PATH+" '"    EXEC(@EX)END -----------------------------backing up the database---------------------------DECLARE @BAKFILE NVARCHAR(255)SET @BAKFILE = @PATH+@DATE+'. DB1. BAK'BACKUP DATABASE [DB1]  to DISK = @BAKFILE  withCOMPRESSIONSET @BAKFILE = @PATH+@DATE+'. DB2. BAK'BACKUP DATABASE [DB2]  to DISK = @BAKFILE  withCOMPRESSION-----------------------------Close xp_cmdshell---------------------------GOsp_configure'xp_cmdshell',0RECONFIGUREGOsp_configure'SHOW Advanced OPTIONS',0RECONFIGUREGO

View Code

The SQL side uses the script to determine if the folder exists and creates