Created Procedure to delete 2 days old backup

Question

Created Procedure to delete 2 days old backup

Mohammad Sufian

Ten Centuries

Points: 1292
More actions
July 2, 2005 at 2:21 am

#186447

Hi,
I had created a script to delete 2 days old backup but this script run under master database .but when i create the procedure and uses like this
exec master..procedurename it gives error
Server: Msg 10019, Level 16, State 1, Line 0
sp_OACreate has not yet been called successfully for this command batch.
Server: Msg 10019, Level 16, State 1, Line 0
sp_OACreate has not yet been called successfully for this command batch.
below is the script i am using in procedure to delete the backup file
if exists ( select name from sysobjects where name='sp_delbackup_db' and type='P')
drop procedure sp_delbackup_db
go
create procedure sp_delbackup_db
as
declare @tr varchar(10)
declare @ds varchar(10)
declare @sd varchar(200)
declare @hr   int
declare @ole_FileSystem int
declare @dbpath varchar(200)
  select @ds=max(backup_set_id) from msdb.dbo.backupfile
   set @tr= @ds-2
       set @dbpath='D:\Database Backup\pubs'+ @tr + '.bkp'
    if @tr>@ds
use master
      EXEC @hr = master..sp_OACreate 'Scripting.FileSystemObject',@ole_FileSystem OUT
      EXEC @hr = master..sp_OAMethod @ole_FileSystem, 'DeleteFile',
       NULL,@dbpath
      EXEC @hr = master..sp_OADestroy @ole_FileSystem
    set @sd='pubs' +@ds+'.bkp'
  --- Print @sd +'........................Deleted'
exec master..sp_delbackup_db
The way i am unsing the procedure
declare @tr varchar(10)
declare @ds varchar(10)
declare @sd varchar(200)
declare @hr   int
declare @ole_FileSystem int
declare @dbpath varchar(200)
  select @ds=max(backup_set_id) from msdb.dbo.backupfile
   set @tr= @ds-2
       set @dbpath='D:\Database Backup\pubs'+ @tr + '.bkp'
    if @tr>@ds
use master
      EXEC @hr = master..sp_OACreate 'Scripting.FileSystemObject',@ole_FileSystem OUT
      EXEC @hr = master..sp_OAMethod @ole_FileSystem, 'DeleteFile',
       NULL,@dbpath
      EXEC @hr = master..sp_OADestroy @ole_FileSystem
    set @sd='pubs' +@ds+'.bkp'
Print @sd +'........................Deleted'
this run fine outside the procedure.
can anybody help me how to remove this error or suggest me some better way.
from
sufian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 1

Why r u creating a procedure for this.When you can do the same from the enterprise manager.When you have scheduled the backup then there only you can specify the criteria that files older than so many days should be deleted.So go for that instead of creating a procedure.

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 2

Hi Mr.Arun,

thanx mr.arun but i like to tell u that try to do something diffrent from others. will DMP will send u the email regarding the deleted backup no.So i delete it from my script and when the delete is complete it will send me a mail about the status.

anyways i had solved it.

thanx

sufian

William Najarro Newbie Points: 3 More actions · Answer 3

Hello, I was wondering what you did to resolve your issue I am having the same problem an I have not been able to find a solution. Your assistance would be appreciated. Thanks.