SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
NviDia
NviDia
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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.
homebrew01
homebrew01
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11900 Visits: 9222
Maybe you could make a 2000 Maint Plan just to delete the old files, and keep your 2005 plan for the backups.



NviDia
NviDia
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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 !
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15261 Visits: 25280
.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
ddezwaan
ddezwaan
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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"
Fred Stemp
Fred Stemp
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 219
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...'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search