[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

how to create a del script?

HI,

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\'


SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb' )

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

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

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor
0
wasabi3689
Asked:
wasabi3689
  • 3
  • 3
1 Solution
 
wasabi3689Author Commented:
or, maybe add more in this backup script to remove the backup older than 7 days.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
BanthorCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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?
0
 
BanthorCommented:
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
0
 
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
0
 
BanthorCommented:
Changing -1. To. -7
Change e:\mssql\backups to your backup location
Execute from CMD prompt or wrap in xp_cmd_shell.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now