none
Real World: Backup Strategy and implementation, how?

    Question

  • Real World: Backup Strategy and implementation, how?

    A quote: 

     

    “Real World:

     

    Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:

     

    • //servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups.
    • //servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups.
    • //servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups.
    • //servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.

     

    Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.”

     

    I understand these concepts, however in ‘the real world’ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3’s rotation.

     

    Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.

     

    I would like full details on this please, as I need to get my head around it.

     

    Thanks

     

    Neil

    Wednesday, January 24, 2007 9:53 AM

Answers

  • You could construct the current media family set using something like

    declare @media nvarchar(150)

    set @media = N'Q:\backups\mymedia' + CAST(DATEPART(ww, GETDATE()) %2 AS Nvarchar(1)) + N'.bck'

    Then you could compare this to the most recent backup to decide whether to add WITH FORMAT.

     

    I still think that it's more useful to simply include the date/time in each backup filename, and run a separate job to delete all backups older than X weeks.

    Tuesday, February 20, 2007 6:21 PM

All replies

  • Can you point me to where this is quoted from?

    This is one method among many for assuring that you always have at least one complete backup set available.

    The critical thing is to not overwrite your ONLY backup set, as would happen if you only used one name.

    Another common solution is to append the date-time to the filename to make dating the backups easy, and then have a separate maintenance job which deletes all backups older than X weeks.

    Thursday, January 25, 2007 12:40 AM
  • Its from MS press, the pocket consultant guide for SQL05
    Thursday, January 25, 2007 12:17 PM
  • Anyone?
    Monday, February 12, 2007 7:55 AM
  • You could construct the current media family set using something like

    declare @media nvarchar(150)

    set @media = N'Q:\backups\mymedia' + CAST(DATEPART(ww, GETDATE()) %2 AS Nvarchar(1)) + N'.bck'

    Then you could compare this to the most recent backup to decide whether to add WITH FORMAT.

     

    I still think that it's more useful to simply include the date/time in each backup filename, and run a separate job to delete all backups older than X weeks.

    Tuesday, February 20, 2007 6:21 PM
  • You can create a database maintenance plan that does backups - a combination of FULL, DIFFERENTIAL and TRANSACTION LOG backups depending on the criticality of your data. You can do what Kevin mentioned in the database maintenance plan so you won't get confused on the backup files. Then, test thoroughly.

    Thursday, February 22, 2007 12:19 PM