MASTER.DBO.XP_CMDSHELL

Create  PROCEDURE [dbo].[USP_Data_BACKUP]( @DATABASE AS VARCHAR(100) ) AS 
BEGIN 
 DECLARE @BK_MAX_COUNT   AS INT,  
  @SQLString   AS NVARCHAR(500), 
  @FNAME    AS VARCHAR(100), 
  @PATH_PREFIX   AS VARCHAR(100), 
  @VARIABLE_FNAME  AS VARCHAR(100), 
  @DEL_VARIABLE_FNAME1 AS VARCHAR(100), 
  @DEL_VARIABLE_FNAME2 AS VARCHAR(100), 
  -- @DATABASE  AS VARCHAR(100), 
  @DEVICENAME   AS VARCHAR(100), 
  @LOG_DEVICENAME  AS VARCHAR(100), 
  @SHELLSTRING  AS VARCHAR(100) 
  
 SET @BK_MAX_COUNT  = 12  
 SET @DEVICENAME  = 'DBK_' + @DATABASE 
 SET @LOG_DEVICENAME  = 'LBK_' + @DATABASE 
 SET @PATH_PREFIX  = 'd:/data_managerdb_backup/' 
 SET @VARIABLE_FNAME  = CONVERT(CHAR(8), GETDATE(), 112)  
     + CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE()))  
     + CONVERT(VARCHAR(2), DATEPART(MINUTE, GETDATE()))  
  
 SET @DEL_VARIABLE_FNAME1 = CONVERT(CHAR(8), GETDATE() - 7, 112) 
 SET @DEL_VARIABLE_FNAME2 = CONVERT(CHAR(8), GETDATE() - 7, 112) 
  
  
 ---------------------------------------------------------- 

--建立d:/data_managerdb_backup/Trans
 SET @SHELLSTRING = 'MKDIR ' + @PATH_PREFIX + 'Trans' 
 EXEC MASTER.DBO.XP_CMDSHELL @SHELLSTRING 
  

--建立d:/data_managerdb_backup/Keeping
 SET @SHELLSTRING = 'MKDIR ' + @PATH_PREFIX + 'Keeping' 
 EXEC MASTER.DBO.XP_CMDSHELL @SHELLSTRING  

--移動d:/data_managerdb_backup/Trans檔案夾下DBK_開頭的備份檔案到d:/data_managerdb_backup/Keeping
 SET @SHELLSTRING = 'MOVE /Y ' + @PATH_PREFIX + 'Trans/' +  @DEVICENAME + '_*.* ' +  @PATH_PREFIX + 'Keeping' 
 EXEC MASTER.DBO.XP_CMDSHELL @SHELLSTRING 
 

--移動d:/data_managerdb_backup/Trans檔案夾下LBK_開頭的備份檔案到d:/data_managerdb_backup/Keeping
 SET @SHELLSTRING = 'MOVE /Y ' + @PATH_PREFIX + 'Trans/' +  @LOG_DEVICENAME + '_*.* ' +  @PATH_PREFIX + 'Keeping' 
 EXEC MASTER.DBO.XP_CMDSHELL @SHELLSTRING 
 ----------------------------------------------------------------------------------------------- 
  
 ---------------------------------------------------------------------- 

--刪除d:/data_managerdb_backup/Keeping檔案夾中一周前的DBK_開頭的備份檔案
 SET @SHELLSTRING = 'DEL ' + @PATH_PREFIX + 'Keeping/' + @DEVICENAME + '_' + @DEL_VARIABLE_FNAME1 + '*.BAK' 
 EXEC MASTER.DBO.XP_CMDSHELL @SHELLSTRING 
  

--刪除d:/data_managerdb_backup/Keeping檔案夾中一周前的LBK_開頭的備份檔案
 SET @SHELLSTRING = 'DEL ' + @PATH_PREFIX + 'Keeping/' + @LOG_DEVICENAME + '_' + @DEL_VARIABLE_FNAME2 + '*.BAK' 
 EXEC MASTER.DBO.XP_CMDSHELL @SHELLSTRING 
 ----------------------------------------------------------------------------------------------- 
  
 ----------------------------------------------------------------------------------- 
 SET @SQLString = N'SELECT * FROM SYSDEVICES WHERE NAME = ''' + @LOG_DEVICENAME + '''' 
 EXEC SP_EXECUTESQL @SQLString 
  
 IF( @@ROWCOUNT > 0 ) 
 BEGIN 
  -- 存在則刪除該磁碟備份檔案
  EXEC SP_DROPDEVICE @logicalname = @LOG_DEVICENAME 
 END 
  
 -- 
 SET @FNAME = @PATH_PREFIX + 'Trans/' + @LOG_DEVICENAME + '_' + @VARIABLE_FNAME + '.BAK' 
--增加新的磁碟備份檔案 

EXEC SP_ADDUMPDEVICE 'DISK', @LOG_DEVICENAME, @FNAME 
  
 SET @SQLString = N'BACKUP LOG ' + @DATABASE + ' TO ' + @LOG_DEVICENAME + ' WITH NOINIT' 

--備份LOG檔案
 EXEC SP_EXECUTESQL @SQLString 
 ------------------------------------------------------------------------------------------------ 
 
 -- -------------------------------------------------------------------------------- 
 SET @SQLString = N'SELECT * FROM SYSDEVICES WHERE NAME = ''' + @DEVICENAME + '''' 
 EXEC SP_EXECUTESQL @SQLString 
  
 IF( @@ROWCOUNT > 0 ) 
 BEGIN 
  --   
  EXEC SP_DROPDEVICE @logicalname = @DEVICENAME 
 END 
  
 SET @FNAME = @PATH_PREFIX + 'Trans/' + @DEVICENAME + '_' + @VARIABLE_FNAME + '.BAK' 
 EXEC SP_ADDUMPDEVICE 'DISK', @DEVICENAME, @FNAME 
  
 SET @SQLString = N'BACKUP DATABASE ' + @DATABASE + ' TO ' + @DEVICENAME + ' WITH NOINIT' 

--備份資料檔案
 EXEC SP_EXECUTESQL @SQLString 
 ------------------------------------------------------------------------------------------------ 
 
END