Backup and Housekeeping with Maintenance Plans

  • Comments posted to this topic are about the item Backup and Housekeeping with Maintenance Plans

    Gethyn Elliswww.gethynellis.com

  • I usually perform the clean up before the backups, that way, it will make the most efficient use of disk space and backups are less likely to fail due to lack of space.

  • Taggs (1/26/2011)


    I usually perform the clean up before the backups, that way, it will make the most efficient use of disk space and backups are less likely to fail due to lack of space.

    That's a fair point the only thing I would say to that is make sure you don't leave yourself in a situation where you deleted the backup and don't have a new one. (If you only keep one backup)

    Gethyn Elliswww.gethynellis.com

  • Good article and led me to check our backup plans set up by a contractor. Discovered that there is no clearing of the backup and restore history and msdb has grown from 12Mb to over 3Gb!

    Thanks for the heads up.

  • Yes, We store 2 days SQL backups locally but also copy these backups on to our 3rd party corporate backup system.

  • Martin Hawley (1/26/2011)


    Good article and led me to check our backup plans set up by a contractor. Discovered that there is no clearing of the backup and restore history and msdb has grown from 12Mb to over 3Gb!

    Thanks for the heads up.

    I'm pleased you found it useful.

    Gethyn Elliswww.gethynellis.com

  • Nice article!

  • Thank you! I'd not looked at the History Cleanup task, so I didn't know what it might do (yes, I'm rather new to managing an SQL Server, and don't have a mentor)

    So, I took a look at a maintenance plan I had set up to backup several DBs on a server, and checked the msdb size. 108GB!! So I added in a History cleanup at the end, and will see if that shrinks it down tonight when the plan executes!

    Thank you from a novice!

    Jason A.

  • Thanq for your article.

  • jasona.work (1/26/2011)


    Thank you! I'd not looked at the History Cleanup task, so I didn't know what it might do (yes, I'm rather new to managing an SQL Server, and don't have a mentor)

    So, I took a look at a maintenance plan I had set up to backup several DBs on a server, and checked the msdb size. 108GB!! So I added in a History cleanup at the end, and will see if that shrinks it down tonight when the plan executes!

    Thank you from a novice!

    Jason A.

    Jason, just a word of warning make sure that the cleanup task when deleting 108GB of data does not block other scheduled backups occuring, it maybe an idea to cleanup in smaller chunks instead of one large hit.

    Gethyn Elliswww.gethynellis.com

  • jasona.work

    Just something to think about. Consider seperating out a database backup maintenance plan. If the plan fails you may end up with no backups at all! :crazy: If you seperate them out tand one plan fails at least you get the other databases backed up.

  • A good basic article about of two of the four major backup problems I commonly see:

    1) Not having backups at all

    2) Not having transaction log backups at all (unlimited log file growth)

    3) Not cleaning up after your backups (unlimited backup size growth)

    4) Not restoring your backups as a test

    4a) Not doing PITR restores as a test

    I'd at least put a note about T-Log backups in your article; many accidental DBA's overlook the absolute necessity of the for Full and Bulk-Logged recovery model databases.

  • Nadrek, Thanks for your comment, this article was meant to be about maintenance/housekeeping of backup files. The points you raise are good points and although its not a 'how to...' article I do have a blog post here http://www.gethynellis.com/2010/10/sql-server-recovery-models.html that looks at the SQL Server recovery models and what you need to do in each.

    Gethyn Elliswww.gethynellis.com

  • Nice, Gethyn. But i like to run DBCC Checkdb as first step... If checkdb fails, the job quits and you don't end up backing up a corrupt database.

  • nice article

    Thanks

    [font="Comic Sans MS"]Rahul:-P[/font]

Viewing 15 posts - 1 through 15 (of 34 total)

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