February 8, 2013 at 9:44 am
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.
February 9, 2013 at 3:31 pm
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
February 11, 2013 at 7:05 am
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.
February 11, 2013 at 8:00 am
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
February 11, 2013 at 8:56 am
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.
September 28, 2016 at 11:26 pm
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! 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply