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