Technical Article

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 + '''')

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating