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: Wednesday, September 10, 2014 1:06 PM
Points: 2,827, Visits: 8,479
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: Thursday, September 18, 2014 7:50 PM
Points: 5,322, Visits: 25,235
.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: Tuesday, August 19, 2014 6:58 AM
Points: 18, Visits: 150
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
Southern Insurance Underwriters, Inc.

'...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