sql server 定時備份資料庫

標籤：comm   共用   time   set   時間   password   sub   Database Backup   --   

CREATE  PROCEDURE [dbo].[SP_DBBackup_EveryNight_Local] @cycle INT,---儲存周期@IsLocal INT,---是否為本地  0表示是 1表示否@SavePath NVARCHAR(100),---檔案儲存路徑 \\192.168.19.102\DataBaseBack@DBPrefix NVARCHAR(50),---組建檔案的首碼@Server VARCHAR(50),---伺服器IP  192.168.19.102@UserName VARCHAR(50),---登入伺服器使用者名稱  administrator@PassWord VARCHAR(20)---登入伺服器密碼   Soft_2016AS    BEGIN          DECLARE @backpath2 VARCHAR(100)          DECLARE @command VARCHAR(200)                   --建立暫存資料表        CREATE TABLE #Filetabel            (              FILEPATH VARCHAR(100) NULL            )    --調用dos命令登入伺服器    IF @IsLocal = 1    BEGINSET @command = ‘net use ‘ + @SavePath + ‘ ‘ + @PassWord + ‘ /user:‘ + @Server + ‘\‘ + @UserName         EXEC master..xp_cmdshell @command      END        --將共用目錄所有檔案名稱添加至暫存資料表        SET @command = ‘dir /b ‘ +  @SavePath        INSERT  INTO #Filetabel                EXEC master..xp_cmdshell @command                        DELETE  FROM #Filetabel        WHERE   FILEPATH IS NULL                WHILE EXISTS ( SELECT   *                       FROM     #Filetabel )            BEGIN                DECLARE @fileName VARCHAR(100)                SET @fileName = ( SELECT TOP 1                                            *                                  FROM      #Filetabel                                  ORDER BY  FILEPATH                                )--定義變數擷取檔案時間                DECLARE @fileTime VARCHAR(20)                DECLARE @fDateTime DATETIME                IF @fileName IS NOT NULL                    BEGIN                        SET @fileTime = SUBSTRING(@fileName, 17, 8)                        SET @fDateTime = CONVERT(DATETIME, @fileTime)                        DECLARE @lastTime DATETIME                        SET @lastTime = DATEADD(DAY, [email protected],                                                CONVERT(VARCHAR(10), GETDATE(), 111))                        IF @fDateTime <= @lastTime                            BEGIN --刪除備份                                DECLARE @filePath VARCHAR(100)                                SET @filePath = ‘del ‘ + @SavePath + ‘\‘ + @fileName                                EXEC master..xp_cmdshell @filePath                            END                                                        --刪除該條資料                                DELETE  FROM #Filetabel                                WHERE   FILEPATH = @fileName                    END            END                        --刪除暫存資料表                                DROP TABLE #Filetabel                                    --將Database Backup到伺服器                                SET @backpath2 = @SavePath + ‘\‘ + @DBPrefix + ‘_‘ + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),                                              ‘-‘, ‘‘) + ‘_back.bak‘                                  BACKUP DATABASE [LGS] TO [email protected] WITH INIT ,FORMAT       END GO

　　

執行EXEC [dbo].[SP_DBBackup_EveryNight_Local] 5,0,‘F:\LGSCMS_20141117_Back‘,‘LGS‘,‘‘,‘‘,‘‘

sql server 定時備份資料庫