none
Advice needed on backup strategy and implementation RRS feed

  • Question

  • This is my plan: Full database backup is performed at 5AM every day. Then log backups are taken every 30 minutes starting from 5:30AM till 11:30PM

    What I want to achieve is to have all backups for the same day to be contained in the same file on the disk. That is on Feb 18 2010 file c:\db_backup_02_18_10.BAK will be created at 5AM with full database backup. All backup logs that occur on Feb 18 will be appended to the same file. Next day c:\db_backup_02_19_10.BAK will be created at 5AM.

    Is this "correct" approach? The reason I like it is that everything needed to restore to any point in time for specific day is contained in one file and can be copied easily to a different computer.

    I tried to achieve this result via Maintenance Plan wizard by creating one task for full backup (scheduled once a day at 5AM) and another task for log backup (scheduled every 30 minutes starting from 5:30AM till 11:30PM) (both tasks are in the same maintenance plan). This, however, saves result from each job in a separate file called $DBNAME_backup_YYYY_MM_DD_HHMMSS_NNNNNNN.trn (for transaction log backup; will probably have extension .BAK for full backup)

    Can this even be done via Maintenance plan?

    I think I can achieve desired result via SSIS where I can generate file name programmatically and pass it as a parameter to "BACKUP DATABASE ..." or "BACKUP LOG ..." statements but would like to know if this is recommended approach before proceeding with implementation


    Wednesday, February 17, 2010 9:07 PM

Answers

  • You can't use Maint plans for such a strategy. Maint plans are designed to create a new file for each time a backup is performed. Then you add a Maint Cleanup task to remove old files. The other option for maint plans is to backup to same file (with either append or overwrite) - with no help for backup generation handling.

    If you want to do anything else than above, you are in for roll your own. This might be a starter: http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by JoeSchmoe115 Thursday, February 18, 2010 7:36 PM
    Thursday, February 18, 2010 6:59 PM
    Moderator

All replies

  • Hi,

    I guess you need to redisgn your backup strategy, this way you will not be able to have point-in-time recovery. What happened if your database server crashes between 2~3 AM?  What you should do is, schedule t-log backup for every 30 minutes.
    Hemantgiri S. Goswami | http://www.sql-server-citation.com/ | http://www.surat-user-group.org/ -- Surat SQL Server User Group
    Thursday, February 18, 2010 9:23 AM
    Moderator
  • You can't use Maint plans for such a strategy. Maint plans are designed to create a new file for each time a backup is performed. Then you add a Maint Cleanup task to remove old files. The other option for maint plans is to backup to same file (with either append or overwrite) - with no help for backup generation handling.

    If you want to do anything else than above, you are in for roll your own. This might be a starter: http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by JoeSchmoe115 Thursday, February 18, 2010 7:36 PM
    Thursday, February 18, 2010 6:59 PM
    Moderator