Problems with sp_dropdevice

Question

Post reply

Problems with sp_dropdevice

tibby

SSC Veteran

Points: 207
More actions
May 13, 2004 at 4:37 am

#108248

I have a backup script that creates a backup device for each day, and uses it for TLog backups. Well, the script that I'm having problems with is the one that goes back and deletes any device older than 7 days, including datafiles. here's the error I get:

Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'sp_dropdevice'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

And here's my entire SP script:

-- This will delete the BackupDevice for the last week
use master
--Declare Variables
SET NOCOUNT ON
DECLARE @DeviceName varchar(255)
DECLARE @File varchar(255)
DECLARE @Creation varchar(255)
DECLARE @OldPlus varchar(255)

-- Declare the cursor
DECLARE bud CURSOR FOR
SELECT BackupDeviceName,
         BackupDeviceFile,
     BackupDeviceCreation
FROM BackupDevice
-- Open the cursor
OPEN bud
-- Loop through the cursor
FETCH NEXT
   FROM bud
   INTO @DeviceName,
        @File,
        @Creation
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OldPlus = DATEADD(day,7,@Creation)
IF @OldPlus < @Creation
  BEGIN
   sp_dropdevice @DeviceName,'DELFILE'
   GO
   DELETE BackupDeviceName,
          BackupDeviceFile,
             BackupDeviceCreation
   FROM BackupDevice
   WHERE BackupDeviceName = @DeviceName AND
   BackupDeviceFile = @File AND
   BackupDeviceCreation = @Creation
  END
END
CLOSE bud
DEALLOCATE bud

Any insight would be very much appreciated.

Thank You,
tibby

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Try changing sp_dropdevice @DeviceName,'DELFILE'

to

execute sp_dropdevice @DeviceName,'DELFILE'

/Kenneth

tibby SSC Veteran Points: 207 More actions · Answer 2

Thank you for the help. I also had another small problem with the fetching, but got it solved. Here's the final version of the script that is working like a wet dream:

-- This will delete the BackupDevice for the last week

use master

--Declare Variables

DECLARE @DeviceName varchar(255)

DECLARE @File varchar(255)

DECLARE @Creation datetime

DECLARE @OldPlus datetime

-- Declare the cursor

DECLARE bud CURSOR FOR

SELECT BackupDeviceName, BackupDeviceFile, BackupDeviceCreation

FROM BackupDevice

ORDER BY BackupDeviceCreation

-- Open the cursor

OPEN bud

-- Loop through the cursor

FETCH NEXT FROM bud

INTO @DeviceName, @File, @Creation

WHILE @@FETCH_STATUS = 0

BEGIN

SET @OldPlus = DATEADD(day, 7, @Creation)

IF convert(varchar(50), @OldPlus, 101) <= convert(varchar(50),getdate(),101)

BEGIN

EXEC sp_dropdevice @DeviceName, DELFILE

DELETE FROM BackupDevice

WHERE BackupDeviceName = @DeviceName AND BackupDeviceFile = @File AND BackupDeviceCreation = @Creation

END

FETCH NEXT FROM bud

INTO @DeviceName, @File, @Creation

END

CLOSE bud

DEALLOCATE bud

Thank you again, I never would have gotten it to work without your insight.

tibby

chen kirsch SSC-Addicted Points: 431 More actions · Answer 3

what about the physical file??? i dont see any action to delete it?? what will happens when the DBbackups fill the HD??

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

To see the answer to that question, please open up BOL and search for 'sp_dropdevice'

/Kenneth

chen kirsch SSC-Addicted Points: 431 More actions · Answer 5

chen kirsch

SSC-Addicted

Points: 431

May 23, 2005 at 4:45 am

#560457

thanks for the reply my bad

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

No problems.

It's just that many forget how good BOL is, so I use to redirect there rather than just state the answers. In BOL you can most of the times not only read how, but also why stuff is like stuff is.

/Kenneth