Backup Maintenance

  • I have my plan set to backup every 24 hours and delete all backups Less than 23 Hours. I only have room for one backup. Is there a way to have the Maintenance job delete the previous backups before it backs up the current database.

  • No - the backup deletes old backups after it has successfully completed the current backup. If it didn't, you could end up with the situation where you don't have a backup.

    If you are desparate, you could use xp_cmdshell to delete the old backup file prior to starting your new backup but only do this if you are very confident that your backups work.

    Jeremy

  • I'd go for a bigger hard drive for that seems very risky

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Its not that risky when you have a system installed that notifies you when a backup fails. If it fails, we resolve the problem.

    Later in the day, the backups are sent to tape. So we already have a copy of the previous days backup on tape. Thus the need to only have one copy on disk.

  • aha 🙂

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • You could always backup to the same backup file name each time, and use the 'WITH INIT' clause of the BACKUP command to overwrite the file.

  • Is there a way to do this in the Maintenance plan (EXECUTE master.dbo.xp_sqlmaint ), so it will clean up the history reports, etc?

    quote:


    You could always backup to the same backup file name each time, and use the 'WITH INIT' clause of the BACKUP command to overwrite the file.


  • I don't think you can do that with a Maintenance Plan, but you could easily do it with a SQL Agent job.

    Just create a job to execute the command:-

    backup database dbname to disk='backupfilename' with init

  • You can set the number of days/hours you want the reports to be kept and rest are deleted on a periodic basis. Did you mean backup history from MSDB database?

    Note: If you change the present maint plan, the changes might not take affect( i had experienced that) you need recreate the maint job.

    Good Luck.

Viewing 9 posts - 1 through 8 (of 8 total)

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