Deleting old backups with transact SQL
-
I was wondering if someone can help me with transact sql here. I would like to take a complete backup of database xyz on sunday followed by a differential backup every other day during the week. However I want to either overwrite files 10 days or older or delete them automatically.
I have the transact sql for backups but dont know how to delete or overwrite old backups.
thanks
-
As far I know the delete old files setup on database maintenance plan never works because a bug that shoudl be fixed but I don't know when, base on that I have the same problem as you and I created this:
*** To delete old backup files - just change the directories as yours *****
CREATE TABLE #Direc(result varchar(500))
declare @direc varchar(500)
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\admindb\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\BESMgmt\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\caisqlp\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\CE10\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\ce10test\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\citrix ds\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\FLSharpShooters\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\InvisionWIP_5_04_05SQL\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\Master\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\MeetingPlanner\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\MobileCastDB\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\model\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\msdb\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\Nortwind\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\OEAdmin\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\OEModels\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\ProjectServerASProdDB\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\ProjectServerProdDB\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\pubs\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SalesHistory\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SalesSWSBPX\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SoutherwineOnline\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SouthernWineOnline_staging\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\STS_SWSSP_1\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\STS_SWSSP_895954\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\STSSBPXv2\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SWSIntenet_Prod\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SWSIntranet_Prod\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SWSUDB\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\SWSUDBTrans\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\TivoliSRM\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\Topazmaster\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\UPSLT\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\UPSLTNY\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\wslogdb55\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\wslogdb61\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\WSSConfigdb\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\WSSProjectDocProdDB\*.BAK')
insert into #Direc (result) values ('\\swsgsbackup\sql_logs\swssql1\WebServicesMetrics\*.BAK')
declare @Directname varchar(300)
declare direct_cursor cursor for
select rtrim(result) from #Direc order by 1 asc
open direct_cursor
Fetch next from direct_cursor into @DirectName
select @DirectName=ltrim(@DirectName)
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #FileList(result varchar(255))
DECLARE @STR varchar(1000),@str1 varchar(1000),@str2 varchar(1000), @BackupFileName varchar(500), @Counter int
SET @Counter = 1
-- read the files
--SELECT @STR = 'insert into #FileList exec master..xp_cmdshell "dir /B /s \\swsgsbackup\sql_logs\swssql1\admindb\*.bak"'
SELECT @STR = 'insert into #FileList exec master..xp_cmdshell "dir /B /s ' + @DirectName + ' "'
exec(@str)
select * from #FileList
DECLARE result_cursor CURSOR FOR
select * from #FileList order by 1 desc
OPEN result_cursor
FETCH NEXT FROM result_cursor into @BackupFileName
WHILE @@FETCH_STATUS = 0
BEGIN
If @Counter > 1 BEGIN
SELECT @STR = 'exec master..xp_cmdshell "del ' + @BackupFileName + '"'
SELECT @STR
exec(@str)
END
SET @Counter = @Counter + 1
FETCH NEXT FROM result_cursor into @BackupFileName
END
--end loop
--clean up
CLOSE result_cursor
DEALLOCATE result_cursor
DROP TABLE #FileList
FETCH NEXT FROM direct_cursor into @DirectName
select @DirectName=ltrim(@DirectName)
END
--clean up
CLOSE direct_cursor
DEALLOCATE direct_cursor
DROP TABLE #Direc
hope this helps
-
I proposed a framework for doing this in my article:
http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp
You can probably modify it as needed for your uses.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply