Database Backup and delete previous day backup
Database Backup and delete previous day backup
-- Differential Backup
set nocount on
declare @dbname varchar(255)
declare @dumplogname varchar(255)
declare @dumppath varchar (30)
select @dumppath = 'H:\Backup\'
declare dblist_cursor cursor
for select name
from sys.databases
where name not in ('QuestSoftware','model', 'AdventureWorksDW', 'AdventureWorks', 'tempdb','master','msdb')
open dblist_cursor
fetch next from dblist_cursor into @dbname
while @@fetch_status = 0
begin
select @dumplogname = @dumppath + @dbname + '_diff_'
+ cast(datepart(year, getdate()) as varchar(10))
+ replace(str(cast(datepart(month, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(day, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(hour, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(minute, getdate()) as varchar(10)), 2, 0), ' ', '0')
+ '.BAK'
BACKUP DATABASE @dbname TO DISK = @dumplogname
WITH DIFFERENTIAL, INIT, NOUNLOAD, NOSKIP, STATS = 20, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @dumplogname
WITH FILE = 1, NOUNLOAD
fetch next from dblist_cursor into @dbname
end
deallocate dblist_cursor
-- Delete previous backup
set nocount on
declare @dumplogpattern varchar(255)
declare @dumppath varchar (30)
declare @todayless1 datetime
select @dumppath = 'H:\BACKUP\'
select @todayless1 = dateadd(day, -1, getdate())
select @dumplogpattern = @dumppath + '*_diff_'
+ cast(datepart(year, @todayless1) as varchar(10))
+ replace(str(cast(datepart(month, @todayless1) as varchar(10)), 2, 0), ' ', '0')
+ replace(str(cast(datepart(day, @todayless1) as varchar(10)), 2, 0), ' ', '0')
+ '*.BAK'
print @dumplogpattern
EXEC ('xp_cmdshell ''del ' + @dumplogpattern + '''')