Go Premium for a chance to win a PS4. Enter to Win

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

how to delete a .bak file using a job in sqlserver

how to delete a .bak file using a job in sqlserver.let us assume the backup is in c:\software.i have to delete .bak file.can some one tell the command.i have to keep it in the job.
0
vijay11
Asked:
vijay11
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can either use xp_cmdshell command to delete your *.bak files from the specified path or if you are taking regular backups using maintenance plans, then you can specify the expiry set for the backup files so that age old files would be deleted automatically by the Maintenance plan itself.
0
 
vijay11Author Commented:
i want this in sqlserver 2000.let ud suppose the files are in c:\software and i have to delete files older than 20 hrs.can you please write the command
0
 
rob_farleyCommented:
Vijay,

Try using a maintenance plan, configuring the CleanUp History section of it. It's very easy to do.

Rob
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
vijay11Author Commented:
if we use maintainance plan(sql 2000).we can not delete backups untill the backup jobs are totally completed.i want a script using xp_cmdshell to delete back ups older than 20 hrs
0
 
rob_farleyCommented:
Is PowerShell okay? Otherwise, you'll need VBScript, which I'm less keen on these days.

dir c:\software\*.bak | where-object -filter {$_.LastWriteTime -lt (Get-Date).AddHours(-20)} | % {$_.Delete()}

Hope this helps,

Rob
0
 
RiteshShahCommented:
well you can do it by date time calculation. I used to create backfile with specified structure.

like:

backupfilename=@DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.0'+'.bak';

I used to run one job to delete one day old backup from specified path. The script I am using is below. you can modify it as per your need. I am deleting 24 hours old backup.




DECLARE @BackupFolder2DaysBack AS varchar(250); 
DECLARE @BackupFolder1DayBack AS varchar(250);
DECLARE @Path AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @PrevDT AS datetime;
DECLARE @DBName AS varchar(250);
SET @Path = 'E:\BACKUP\';
SET @CurDT = DateAdd(Day,-1,getdate());
SET @PrevDT = DateAdd(Day,-2,getdate());
-- Loop through databases
DECLARE delExpDatabases CURSOR
FOR
 select name from master..sysdatabases where name not in ('tempdb','Northwind','pubs','model','test1','test2','ASPState') 
OPEN delExpDatabases
FETCH NEXT FROM delExpDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
 SET @BackupFolder2DaysBack = @Path + @DBName + '\' + CONVERT(varchar(10), @PrevDT, 110);
 SET @BackupFolder1DayBack = @Path + @DBName + '\' + CONVERT(varchar(10), @CurDT, 110); 
 DECLARE @cmdStr AS varchar(250);
 SET @cmdStr = 'rmdir /Q /S '+@BackupFolder2DaysBack
 --print @cmdStr
 EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
 SET @cmdStr = 'ERASE /Q /S '+@BackupFolder1DayBack+'\*.dif '+@BackupFolder1DayBack+'\*.trn' 
 --print @cmdStr
 EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
   FETCH NEXT FROM delExpDatabases INTO @DBName
END
CLOSE delExpDatabases
DEALLOCATE delExpDatabases

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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