"Could not find stored procedure 'master.dbo.xp_delete_file'

  • Hi Experts,

    Using maintenance plan, i'm using sqlserver 2005 to backup & purge older backup for sqlserver 2000 i'm facing this error.

    "Could not find stored procedure 'master.dbo.xp_delete_file'

    The Schedule can take the backup but fails while purging. It seems, sqlserver 2000 donot have this procedure.

    Can anyone guide me, how can I achieve this tasks from sqlserver 2005 ; as I'm centrally managing backup from this version.

    Thanks.

  • Maybe you could make a 2000 Maint Plan just to delete the old files, and keep your 2005 plan for the backups.

  • Thanks for the idea.

    Though I got another idea , ie to create connection from 2005 instance to do the same task on 2000 instance.

    It worked !

  • .dbo.xp_delete_file' is actually implimented as a DDL named xpstar90.dll.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • "If everything seems to be going well, you have obviously overlooked something. " - Words to live by!

    I have found putting this in a batch file and scheduled task is quite effective;

    forfiles -p "C:\SQLBACKUP\Differential" -s -m *.* /D -2 /C "cmd /c del @path"

  • Not sure this helps, but I have several 2000 servers in Production still and I had to right a couple of queries to do my incremental backups and delete files after a certain period of time.

    For a single database:

    DECLARE @date varchar (14),

    @DBName_Path varchar (255),

    @File varchar (260),

    @cmd varchar (255),

    @database varchar(25)

    set @date = convert (varchar(12) , getdate(), 112) + substring (convert (varchar(12) , getdate(), 114),1,2) + substring (convert (varchar(12) , getdate(), 114),4,2) + substring(convert (varchar(12) , getdate(), 114),7,2)

    set @DBName_Path = 'Z:\Backups\DATA\<Your DBName>\<Your DBName>_Incremental_' + @date + '.BAK'

    set @database = 'ImageRight'

    select @DBName_Path, @database

    BACKUP LOG @database TO DISK = @DBName_Path

    GO

    xp_cmdshell 'forfiles /p Z:\Backups\DATA\<Your Folder>\ /m *.bak /c "cmd /c del @file " /d -8'

    For multiple databases:

    USE master

    DECLARE @DBName VARCHAR(100)

    -- CURSOR to lookup DBNames in master DB excluding system DBs in results set

    DECLARE DBBackup_Cursor CURSOR LOCAL STATIC FOR

    -- Get DBNames for CURSOR

    SELECT CATALOG_NAME

    FROM INFORMATION_SCHEMA.SCHEMATA

    WHERE CATALOG_NAME NOT IN ('master', 'tempdb', 'model', 'msdb')

    ORDER BY CATALOG_NAME

    -- Start CURSOR

    OPEN DBBackup_Cursor

    FETCH NEXT FROM DBBackup_Cursor INTO @DBName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    FETCH NEXT FROM DBBackup_Cursor INTO @DBName

    -- Incremntal Backup Process

    DECLARE @date VARCHAR (14),

    @DBName_Path VARCHAR (255),

    @File VARCHAR (260),

    @cmd VARCHAR (255),

    @database VARCHAR(100),

    @FileDelete VARCHAR(100)

    set @date = CONVERT (VARCHAR(12) , GETDATE(), 112) + SUBSTRING (CONVERT (VARCHAR(12) , GETDATE(), 114),1,2) + SUBSTRING (CONVERT (VARCHAR(12) , GETDATE(), 114),4,2) + SUBSTRING(CONVERT (VARCHAR(12) , GETDATE(), 114),7,2)

    set @DBName_Path = 'F:\BACKUP\DATA\' + @DBName + '\' + @DBName + '_Incremental_' + @date + '.BAK'

    set @database = @DBName

    -- This will display path and filename during a test

    -- select @DBName_Path, @database

    -- For testing path, comment out the below line prior to executing script

    BACKUP LOG @database TO DISK = @DBName_Path

    -- To test comment out the below line prior to executing script

    SET @FileDelete = 'xp_cmdshell' + '''' + ('forfiles /p F:\BACKUP\DATA\') + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'''

    EXECUTE (@FileDelete)

    FETCH NEXT FROM DBBackup_Cursor INTO @DBName

    -- Uncomment to test xp_cmdshell command is formated fcorrectly

    -- SET @FileDelete = SET @FileDelete = 'xp_cmdshell' + '''' + ('forfiles /p F:\BACKUP\DATA\') + @DBName + '\ /m *.bak /c "cmd /c del @file " /d -8'''

    -- SELECT @FileDelete

    -- CURSOR END

    END

    CLOSE DBBackup_Cursor

    DEALLOCATE DBBackup_Cursor

    I put each of these in as Steps in a Job and they are working without issue. If these help out, I'd love to hear. Good luck!

    Frederick (Fred) J. Stemp, Jr.
    Database Administrator / Database Developer
    Dealer Funding, LLC

    '...if they take my stapler then I'll set the building on fire...'

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

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