Post reply

UNABLE TO DELETE OLD BACKUPS

  • April 28, 2009 at 9:31 am

    #208886

    I have created a procedure which will create backups of all the databases on the server for a SQL SERVER express instance when called from OSQL.Here is the code I have used to backup:

    DECLARE @str1 varchar(50), @str3 varchar(50), @strSQL Varchar(500),@strDB Varchar(50)

    SELECT @str1='BACKUP DATABASE ', @str3='.bak'' WITH RETAINDAYS = 5, INIT,SKIP, STATS = 10'

    Select @strDB =Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and databaseproperty(name, 'IsOffline') <> 1

    While @strDB is not null

    begin

    Select @strSQL=@str1+'['+@strDB+']'+@bkplocation+@strDB+REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') +@str3

    Print @strSQL

    Execute (@strSQL)

    Select @strDB = Min(name) from master..sysdatabases WHERE NAME not in ('model','msdb','tempdb') and name > @strDB and databaseproperty(name, 'IsOffline') <> 1

    end

    Unfortunately The backups does not get deleted after 5 days.Can any of you SQL GURUS help me solve this?

    thanks in advance

  • April 28, 2009 at 10:22 am

    #985297

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic705985-357-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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