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) ---登录服务器密码 2016
AS
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','','',''