0

I'm trying to delete old backups with a scheduled job in SQL Server 2005, running on Windows Vista. Here's my script:

DECLARE @BackupPath NVARCHAR(1000)
DECLARE @DeleteDate DATETIME

SELECT @DeleteDate = DATEADD(DAY, -7, GETDATE())

DECLARE @Paths TABLE (BackupPath NVARCHAR(1000))

INSERT INTO @Paths 
    SELECT '\\192.168.5.20\mssqlserver_backups\SomeDB\' UNION ALL
    SELECT '\\192.168.5.20\mssqlserver_backups\SomeDB2\'

DECLARE DbCursor CURSOR FOR SELECT BackupPath FROM @Paths

OPEN DbCursor;

FETCH NEXT FROM DbCursor INTO @BackupPath;

WHILE @@FETCH_STATUS = 0

BEGIN 

    EXEC master.dbo.xp_delete_file 
        0,
        @BackupPath,
        'bak',
        @DeleteDate,
        0

    FETCH NEXT FROM DbCursor INTO @BackupPath;
END 

I'm running into this error:

(2 row(s) affected) Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter

I suspect that this might have something to do with the (quite old) version of Windows or SQL Server I'm running. What am I missing?

  • First: a warning. SQL Server 2005 is so far out of support, folks here are unlikely to have an instance handy to help. (Also: expect several suggestions that you fix it by upgrading to asupported OS & SQL version. 😁) Anyhow.....That call to xp_delete_file seems ok, except your file extension parameter should be nvarchar, not varchar: N'bak' – AMtwo Dec 12 '19 at 12:11
  • Was it a job that existed before and stopped or somenthing new you're creating? Your code ran fine for me. Remember you're dealing with an unsuported/undocumented feature of SQL Server so unstable behaviour might occur. Do you get the same error if executing just EXEC master.dbo.xp_delete_file 0, N'\\192.168.5.20\mssqlserver_backups\SomeDB\','bak', '2019-12-12 12:29:36.057',0;`` without the rest? Please, try this in a test folder with backups that can be discarded. – Ronaldo Dec 12 '19 at 14:41
  • @AMtwo you wouldn't believe what an upgrade it was to start using 2005 for us. N'bak' didn't help btw, I'm getting the same error. Do you think it might have something to do with the directory being remote, i.e. path starting with `\\`? – Hasan Can Saral Dec 13 '19 at 9:51
  • @Ronaldo It is a new job I'm creating, and getting the same error executing only EXEC master.dbo.xp_delete_file 0, N'\\192.168.5.20\mssqlserver_backups\SomeDB\','bak', '2019-12-12 12:29:36.057',0; – Hasan Can Saral Dec 13 '19 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.