Post reply

delete ackup files

  • August 16, 2011 at 1:26 am

    #243186

    hello dears

    how can i delete backup files older than x days in sqlserver2000?

    i use below t-sql:

    set @olddate = CONVERT(VARCHAR, GetDate() - 30,126)

    set @backupDir = 'E:BackUp'

    set @exttype = 'BAK'

    set @deletefiles = 'master.dbo.xp_delete_file 0,N''' + @backupDir +

    ''',N''' + @exttype + ''',N''' + @olddate + ''',1'

    print @deletefiles

    exec (@deletefiles)

    but i get below error :

    Msg 2812, Level 16, State 62, Line 1

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

    also i could not find XP_DELETE_FILE store procedure in master db of sqlserver2000?

    would you please help me ?

  • August 16, 2011 at 1:32 am

    #1368956

    I don't think this sp is available in sql 2000 . I think the better option is to write a batch file to perform the delete.

    You could also use the DTS package.

    BTW you posted in the wrong category this is a SQL Server 2005 category.

    Jayanth Kurup[/url]

  • August 16, 2011 at 1:39 am

    #1368961

    hnow can delete a backup file in sqlserver2000?

  • August 17, 2011 at 11:57 am

    #1369774

    zsafakhah (8/16/2011)

    hnow can delete a backup file in sqlserver2000?

    Some options for you in no particular order:

    - Use xp_CmdShell to issue a DEL command directly

    - Use xp_CmdShell to call a Windows Batch script that will do the deletes as needed.

    - Use xp_CmdShell to call a PowerShell script that will do the deletes as needed.

    - Use xp_CmdShell to call forfiles.exe to delete old files.

    - Setup a SQL Agent job that will do one of the above tasks on a schedule (i.e. no need for xp_CmdShell).

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

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