Post reply

Creating a Backup & Recovery plan

  • November 7, 2006 at 9:22 am

    #115036

    Hi All,

     

    I am wondering if anyone who has created a functional backup/recovery plan can help me in formulating one.

    Although I know the basics of this, i.e how important the data is, how long can the system be offline etc.

    I would like to consult an expert on this and would also like to know thier views.

    Any contribution would be really appreciated.

    Please see explanation below:

    The following should be taken into consideration

     

    1. The database is almost 3TB in size and would grow considerably over the next couple of years.
    2. The recovery model chosen is the Simple recovery model, the reason is because the loader does thousands of BCP operations mostly Bulk inserts in a day and if a full or bulk logged recovery option was chosen, the transaction log size can increase greatly and might slow down the performance of the database.
    3. The filegroups are on a RAID 5 configuration.
    4. The backup would initially be on disk before being transferred onto tape.
    5. The Current backup strategy is a fullback up once a week and differential backups daily.
    6. Restoring the database takes enormous amount of time, so the restore time of the new database is very crucial.
    7. The  backup/recovery solution be very reliable.
    8. The system is based on SQL 2005
    9. The host operating system is a windows 2003 server

     

     

    Changeable Data

    (5GB in Size)

     

    Filegroup A

     

    Static Data

    (2GB in Size)

     

    Filegroup B

     

    Other Data

    (1GB in Size)

     

    Filegroup C

     

    Primary Data

    (10GB in Size)

     

    Primary Filegroup

    November Data

    (300GB)

    Noverber_Filegroup

    Current Month

    Updated 24/7

     

    October Data

    (300GB)

     

    October_Filegroup

     

    (Rare updates)

    September Data

    (300GB)

     

    September _Filegroup

    (Rare updates)

    August Data

     

    August _Filegroup

    (Rare updates)

    DB

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • November 8, 2006 at 11:55 am

    #670674

    The first thing I would do if possible is take the filegroups off RAID5 and put them on separate RAID1 volumes. The more the better. At least FilegroupA since it gets a lot of writes.

    second, i forgot the details but you can partition the data so when you restore you can restore the critical data first, bring it up and then restore the rest

  • November 9, 2006 at 2:31 am

    #670780

    Thanks for your reply, really appreciated but moving forwards, on the issue of creating a complete backup strategy, would you have any ideas ?

    Thanks

    Kindest Regards,

    John Burchel (Trainee Developer)

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

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