how to create a auto job that deletes the bak file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

how to create a auto job that deletes the bak file

Author

Topic

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-04-17 : 12:54:57

Does the job needs to call a specific procedure that will romove all backup files??

Please Help!!!!!!!!
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-17 : 13:02:34

Well if you are using a maintenance plan to do the backups, then just have it delete the files for you. If you are explicitly calling BACKUP statements in your job, then either initialize your backup so that it gets overwritten or run delete commands using xp_cmdshell. I prefer the initialize way:

BACKUP DATABASE DBName
TO DISK = 'E:\MSSQL\Backup\DBName.bak'
WITH INIT

At my last job, we retained seven days of backups on disk. Each day had a different name (Sunday.bak, Saturday.bak, etc...). So we had a stored procedure that managed the name of the file and always did an INIT in the backup command.

HTH,

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-17 : 13:09:36

Another idea,

Create a VBScript that deletes files older than a specified period, such as 2 days or one week. The VBScript would be faily each to create for this.

If you need help on any of the solutions, just let us know.

Tara

nishithrn
Yak Posting Veteran

58 Posts

Posted - 2003-04-19 : 13:53:48

Hi,

I guess Maintenance Plane is the best bet wherein you can specify to "delete files proir to 'N Number' of days.

bye

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-19 : 14:08:19

I'm not keen on maitenance plans.
To maintain the backup directory you can use xp_cmdshell.
execute a dir command to get all the filenames and attributes into a temp table. Delete the one you don't want to delete then execute a del command for each of the ones left.

It's easier if your backup files are named

DBName_Full_yyyymmdd_hhmm.bak

Then you can just get the filenames and use that.

You will now have a job you can execute independently of the backups and stop it if there is a problem with your tape transfers.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources