SQl自动备份删除

来源：互联网发布：python try用法编辑：程序博客网时间：2024/05/22 02:21

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