Delete multiple backup files with a wildcard (*)?

• WebTechie

  SSChampion

  Points: 11405

  More actions

  February 8, 2013 at 9:44 am

  #288959

  Hello,

  We have a dev server, SQL Server 2008, that has about 20 databases.

  I got a request in to delete all backup files for a specific database and then do a full backup.

  Odd request, but OK since it was from the project manager for that project.

  I can remote on the server, but oddly, not to the backup folder.

  I see that there is a maintenance task that I can add to a maintenance plan, but it allows you to delete only one specific file at a time.

  I'd like to delete all the backup files that begin with a certain database name.

  I copied the SQL from the maintenance plan (view sql). So I see the TSQL for this is:

  EXECUTE master.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\CNDC_CDC_backup_2013_02_01_100004_5309337.trn',N'',N'2013-01-11T10:34:46'

  Anyone have an idea how to run this for all database backup files that begin with CNDC_CDC?

  I would imagine that there are about 30 files to delete with 5 full backup files and about 5-6 transaction log backup files. So not impossible to do one at a time. But since, I'd like to improve my TSQL skills, I wanted to see if there was a more efficient way.

  Thanks for the help.

  Tony

  Things will work out.  Get back up, change some parameters and recode.

• bleroy

  Hall of Fame

  Points: 3052

  More actions

  February 9, 2013 at 3:31 pm

  #1586264

  What about:

  /*

  -- Turn on XP_CMDSHELL if required

  --

  --allow advanced options to be changed.

  EXEC sp_configure 'show advanced options', 1

  GO

  --Update the currently configured value for advanced options.

  RECONFIGURE

  GO

  --Enable XP_CMDSHELL

  EXEC sp_configure 'xp_cmdshell', 1

  GO

  --Update the currently configured value for this feature.

  RECONFIGURE

  GO

  */

  IF OBJECT_ID('myFileList') IS NOT NULL DROP TABLE myFileList

  --Create the table to store file list

  CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256))

  --Insert file list from directory to SQL Server

  DECLARE @PreCommand varchar(256) = 'dir '

  DECLARE @Path varchar(256) = 'C:\delete\' -- change this folder to your folder name

  DECLARE @Command varchar(1024) = @PreCommand + @Path + ' /A-D /B'

  INSERT INTO myFileList

  EXEC MASTER.dbo.xp_cmdshell @Command

  --SELECT * FROM myFileList

  --WHERE [FileName] LIKE 'Full%'

  DECLARE @fileName VARCHAR(256) -- filename for backup

  DECLARE @FullFileName VARCHAR(256)

  DECLARE @DeleteDate nvarchar(50)

  DECLARE @DeleteDateTime datetime

  DECLARE FileCursor CURSOR FOR

  SELECT [FileName] FROM myFileList

  WHERE [FileName] LIKE 'CNDC_CDC%'

  OPEN FileCursor

  FETCH NEXT FROM FileCursor INTO @Filename

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @FullFileName = @Path + @Filename

  set @DeleteDateTime = DateAdd(hh, 0, GetDate()) -- the 0 means that any file older then 'now' will be deleted

  set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

  EXECUTE master.dbo.xp_delete_file 0,@FullFileName,N'',@DeleteDate

  FETCH NEXT FROM FileCursor INTO @Filename

  END

  CLOSE FileCursor

  DEALLOCATE FileCursor

  --Clean up

  DROP TABLE myFileList

  GO

  B

• OTF

  SSCertifiable

  Points: 6104

  More actions

  February 11, 2013 at 7:05 am

  #1586537

  I would be cautious not to delete any existing backup files until I have a new verified/restorable backup although that may well go without saying.

• Stuart Davies

  SSCoach

  Points: 19505

  More actions

  February 11, 2013 at 8:00 am

  #1586587

  OTF (2/11/2013)

  ---

  I would be cautious not to delete any existing backup files until I have a new verified/restorable backup although that may well go without saying.

  +10

  Backup before deleting the old backups 100%

  Bridges, burning etc.

  -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
  There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
  I would never join a club that would allow me as a member - Groucho Marx

• WebTechie

  SSChampion

  Points: 11405

  More actions

  February 11, 2013 at 8:56 am

  #1586626

  Thank you. I will give this a try.

  I really appreciate it. I know on most servers, we are not allowed to cmdshell.

  However, this is a DEV server, so I might be able to get away with it.

  Thanks again.

  Tony

  Things will work out.  Get back up, change some parameters and recode.

• cutiesaszy

  Newbie

  Points: 9

  More actions

  September 28, 2016 at 11:26 pm

  #1903829

  Hi guys, need help on old backup deletion.

  DECLARE FileCursor CURSOR FOR

  SELECT FName FROM myFileList

  WHERE FName LIKE '%_OLD%'

  OPEN FileCursor

  FETCH NEXT FROM FileCursor INTO @filename

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @DeleteDateTime = convert(nvarchar(20),DateAdd(DAY, -7, GetDate())) -- older than 7 days

  EXECUTE master.dbo.xp_delete_file 0,@Path,N'bak',@DeleteDateTime

  FETCH NEXT FROM FileCursor INTO @filename

  END

  CLOSE FileCursor

  DEALLOCATE FileCursor

  So I tried this script, and yeah, it'll delete the files older than 7 days, BUT, it deletes all .bak files.

  I need to have an exemption for filenames with %_%

  I'll have to put in a separate condition to delete .bak files with %_%, older than 90 days.

  Can someone help me on this? Thanks a lot in advance!