CREATE  PROCEDURE [dbo].[SP_DBBackup_EveryNight_Local] @cycle INT,					---保存周期@IsLocal INT,				---是否为本地  0表示是 1表示否@SavePath NVARCHAR(100),	---文件保存路径 \\192.168.19.1\DataBaseBack@DBPrefix NVARCHAR(50),		---生成文件的前缀@Server VARCHAR(50),		---服务器IP  192.168.19.1@UserName VARCHAR(50),		---登录服务器用户名  administrator@PassWord VARCHAR(20)		---登录服务器密码   2016AS    BEGIN          DECLARE @backpath2 VARCHAR(100)          DECLARE @command VARCHAR(200)                   --创建临时表        CREATE TABLE #Filetabel            (              FILEPATH VARCHAR(100) NULL            )    --调用dos命令登录服务器    IF @IsLocal = 1    BEGIN		SET @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, -@cycle,                                                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                                    --将数据库备份到服务器                                SET @backpath2 = @SavePath + '\' + @DBPrefix + '_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),                                              '-', '') + '_back.bak'                                  BACKUP DATABASE [LGS] TO DISK=@backpath2 WITH INIT ,FORMAT       END GO

　　

执行EXEC [dbo].[SP_DBBackup_EveryNight_Local] 5,0,'F:\LGS_Back','LGS','','',''