Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Could not find stored procedure 'master.dbo.xp_delete_file' Expand / Collapse
Author
Message
Posted Thursday, March 17, 2011 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2011 11:19 AM
Points: 2, Visits: 31
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.
Post #1079951
Posted Friday, March 18, 2011 7:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 2,871, Visits: 8,735
Maybe you could make a 2000 Maint Plan just to delete the old files, and keep your 2005 plan for the backups.


Post #1080355
Posted Friday, March 18, 2011 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2011 11:19 AM
Points: 2, Visits: 31
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 !

Post #1080405
Posted Friday, March 18, 2011 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
.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

Before posting a performance problem please read
Post #1080410
Posted Thursday, October 17, 2013 4:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:06 PM
Points: 4, Visits: 55
"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"


Post #1505945
Posted Wednesday, October 23, 2013 10:47 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 27, 2015 10:17 AM
Points: 21, Visits: 193
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...'
Post #1507748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse