SQLServer备份语句/保留指定时间内的备份文件（可用于作业备份）

--完整备份

declare @name varchar(250) 
set @name='D:\BACKUP\ALLBACKUP\' + convert(varchar(50),getdate(),112)+ 'mzyy.bak'

BACKUP DATABASE [mzyy] TO DISK = @name WITH NOFORMAT, INIT, 
NAME = N'mzyy-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

--删除完整备份

declare @dir varchar(100),@str varchar(100),@fileName varchar(30),@filepath varchar(300)
SET @dir='del D:\BACKUP\ALLBACKUP\'
SET @str = LEFT(replace(replace(replace(convert(varchar,getdate()-7,20),'-',''),' ',''),':',''),8)
SET @fileName = 'mzyy.bak'
SET @filepath =@dir +@str + @fileName
exec xp_cmdshell @filepath

--删除差异备份

declare @dirs varchar(100),@strs varchar(100),@fileNames varchar(30),@filepaths varchar(300)
SET @dirs='del D:\BACKUP\DIFFBACKUP\'
SET @strs = LEFT(replace(replace(replace(convert(varchar,getdate()-7,20),'-',''),' ',''),':',''),8)
SET @fileNames = 'mzyy_DIFF.diff'
SET @filepaths =@dirs +@strs + @fileNames
exec xp_cmdshell @filepaths

--差异备份

declare @str varchar(100)
set @str='D:\BACKUP\DIFFBACKUP\'+replace(replace(replace(convert(varchar,getdate(),112),'-',''),' ',''),':','')+'mzyy_DIFF.diff'
BACKUP DATABASE [mzyy] TO DISK=@str
WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,
NAME=N'mzyy差异备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10

注意：如果需要删除备份的话，需要开启 xp_cmdshell 

语句：推荐使用语句（因为，我使用的是  SQL2008R2，没在 SQL的配置工具中找到 SQLServer外围应用配置器）

还有，记得  给 文件夹配置权限（不然无法识别文件夹内的备份文件）

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

--如需关闭只需将“sp_configure 'xp_cmdshell',1”改为“sp_configure 'xp_cmdshell',0”即可。

 

补充：

删除文件夹（包括文件夹内部的文件）

DECLARE @namelist varchar(100),@path VARCHAR(100)
SET @path = 'D:\test\999'
SET @namelist='rd /s /q ' + @path
exec xp_cmdshell @namelist

清空文件夹内部文件（不包含文件夹本身）

declare @p varchar(100)
SET @p='del /Q ' + @path + @fileName
exec xp_cmdshell @p

 获取文件夹文件并形成列表

declare @namelist varchar(100),@path VARCHAR(100)
    SET @path = 'D:\test\999'
    SET @namelist='dir /b ' + @path
    create table #tempFileName (FileName VARCHAR(max))
    INSERT INTO #tempFileName
    (
        FileName
    )
    exec xp_cmdshell @namelist

判断是否是数字

AND PATINDEX('%[^0-9]%', FileName)=0

 共享盘复制：

SET @copyPath = 'copy D:\bak\db\DELL_PVD\DIFFBACKUP\' + @fileName  + ' \\192.168.0.1\Backup\bak\db\DELL_PVD\DIFFBACKUP\' + @fileName
EXEC dbo.xp_cmdshell 'net use  \\192.168.200.5\Backup\bak\db\DELL_PVD\DIFFBACKUP\ "登录密码"   /user:192.168.0.1\登陆账号'
exec xp_cmdshell @copyPath

 

 

 

感谢：https://www.cnblogs.com/zhangq723/archive/2012/03/13/2394102.html

https://www.cnblogs.com/devloper110/articles/1298650.html

http://blog.csdn.net/XU123bin/article/details/71467142

https://www.cnblogs.com/atree/p/SQL_SERVER_xp_cmdshell.html

https://blog.csdn.net/zy835859234/article/details/111906232