SQl自动备份删除

最新推荐文章于 2023-01-12 15:04:26 发布

chenli985

最新推荐文章于 2023-01-12 15:04:26 发布

阅读量453

点赞数

CC 4.0 BY-SA版权

文章标签： sql database disk 数据库 file table

本文链接：https://blog.youkuaiyun.com/chenli985/article/details/5265132

BackupEveryDay
每天进行数据库的差异备份
day
Declare @File Varchar(2000)
Set @File='E:/Databasebackup/njyc_data_diff.BAK'
Backup database njyc_data to Disk=@File with DIFFERENTIAL

WeekBackup
每周进行一次数据库的完全备份，备份文件名为当天日期（njyc_data_年_月_日）

BackupAll
DECLARE @BackupFile Varchar(256)
SET @BackupFile='E:/Databasebackup/'
SET @BackupFile=@BackupFile+'njyc_data_'+Replace(convert(varchar(10),getdate(),120),'-','_')
Backup DataBase njyc_data to Disk=@BackupFile with Init

BackupDiffrent

DECLARE @BackupFile Varchar(256)
SET @BackupFile='E:/Databasebackup/'
SET @BackupFile=@BackupFile+'njyc_data_diff.bak'
Backup DataBase njyc_data to Disk=@BackupFile with Init

EveryWeek

DeleteLastmonth

--判断文件是否存在
create table ##tb(a bit,b bit,c bit)
DECLARE @DelFile Varchar(256)
set @DelFile='E:/Databasebackup/'
set @DelFile=@DelFile+'njyc_data_'+Replace(convert(varchar(10),getdate()-28,120),'-','_')
insert into ##tb
exec master..xp_fileexist @DelFile
--文件存在则删除
if exists(select 1 from ##tb where a=1)
begin
DECLARE @Delsql Varchar(256)
set @Delsql='Del '+ @DelFile
Exec master..xp_cmdshell @Delsql
end

drop table ##tb