how to create a del script?


I have the following backup script to back up all database in my MS SQL 2008. This script works fine. I create a job and attach to it with schedule run. But, now I want to have another script to delete the oldest backup after 7 days in the backup folder. Do you have any sample I can borrow.

Here is the backup script

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

--SET @path = 'C:\Backup\'


FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb' )

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name  

CLOSE db_cursor  
DEALLOCATE db_cursor
Who is Participating?
BanthorConnect With a Mentor Commented:
ok, if you don't care about preserving the last database backup
forfiles /p "E:\MSSQL\Backups" /s /m *.* /c "cmd /c Del @path" /d -1

Open in new window

Where -1 means how many days old to be deleted
wasabi3689Author Commented:
or, maybe add more in this backup script to remove the backup older than 7 days.
Aneesh RetnakaranDatabase AdministratorCommented:
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I Create a table in a Central repository bkp_Files
I populate this table from by using cmd_shell and cursuring recursively through a directory share and executing
Restore Header 

Open in new window

against BAK and TRN files

I have seperate job that identifys the 7 most recent full backups and then deletes older files.

This method preserves the last weeks activity with restore to point in time recovery regardless of how old the files are.

Deleting by date removes archived databases that may be required
wasabi3689Author Commented:
It seems the following code is simplest

REM del_old.bat
REM usage: del_old MM-DD-YYY
for /f "tokens=*" %%a IN ('xcopy *.* /d:%1 /L /I null') do if exist %%~nxa echo %%~nxa >> FILES_TO_KEEP.TXT
for /f "tokens=*" %%a IN ('xcopy *.* /L /I /EXCLUDE:FILES_TO_KEEP.TXT null') do if exist "%%~nxa" del "%%~nxa"

But it doesn't say the directory, correct? where to put?
wasabi3689Author Commented:
I only care about the last 7 day backup or keep last 7 days backup. I don't understand your code. It's run in cmd? why there is no day stated?. I don't want to remove all backup
Changing -1. To. -7
Change e:\mssql\backups to your backup location
Execute from CMD prompt or wrap in xp_cmd_shell.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.