Create Backup Folder & Database Backup File Using Procedure

In this blog create auto folder & auto backup using Xp_Cmdshell.

Create Backup folder & database Backup file using Procedure

In this procedure, we are using xp_cmdshell for creating & copying folder & Backup files to location

STEP 1:  below exec xp_cmdshell RD command used to Remove directory

EXEC xp_cmdshell 'RD D:\SqlBank_Backup_Fol7\ /S /Q'    
    
exec xp_cmdshell  'RMDIR /S /Q D:\SqlBank_Backup_Fol7'  

STEP 2: below command is used to dump the contents of memory

EXEC xp_cmdshell 'MD D:\SqlBank_Backup_Fol7\' 

Below combine procedure

USE [SqlBank]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Procedure    
/*  
-- Author by : Sai P Pathrikar  
-- Blog     : http://saipathrikar.blogspot.com/  
*/  
CREATE PROC[dbo].[Proc_DbBackup_SqlBankServer]    
    
as    
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 @Del varchar(500)

EXEC xp_cmdshell 'RD D:\SqlBank_Backup_Fol7\ /S /Q'  

EXEC xp_cmdshell  'RMDIR /S /Q D:\SqlBank_Backup_Fol7'  

EXEC xp_cmdshell 'RD D:\SqlBank_Backup_Fol7 /S /Q'
    
EXEC xp_cmdshell 'RMDIR /S /Q D:\SqlBank_Backup_Fol7'
-- specify database backup directory

EXEC xp_cmdshell 'MD D:\SqlBank_Backup_Fol7\'
SET @path = 'D:\SqlBank_Backup_Fol7\'  -- 1st Create Folder In
     
-- specify filename format
SELECT @fileDate = Replace(replace(CONVERT(VARCHAR(20),GETDATE(),113),' ','_'),':','_')
   
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases   with(nolock)
WHERE name  IN (
select name from sys.databases
	 where name not in('master','tempdb','model','msdb')
)  -- if Taking New Db Backup Add New Db Name Here
     
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
   FETCH NEXT FROM db_cursor INTO @name
      DECLARE @SQL VARCHAR(1000)
END
  
CLOSE db_cursor
DEALLOCATE db_cursor
  UPDATE Tbl_SysPara SET Sys_ParaExpireDatetime = CURRENT_TIMESTAMP WHERE
  Sys_ID = 20004
return

now execute procedure

EXEC [dbo].[Proc_DbBackup_SqlBankServer] 

Output

Thank you for Reading