Post reply

A Backup Idea Needed ! Plans welcome

  • November 8, 2006 at 5:49 am

    #61939

    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 and SQL 2000

     

     

    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

     

     

    Any Ideas welcomed

    Kindest Regards,

    John Burchel (Trainee Developer)

  • November 13, 2006 at 3:14 pm

    #671461

    In this case it depends how much hardware do you have.

    If you have another server with similar configuration, you may keep the second server in synch with the production server by restoring full and differential backups as they are perfomed on the production system or use Log Shipping (you do not have Full recovery model for Log Shipping)or Database Mirroring (SQL 2005 SP1)

    I would also create and use a DNS alias for the name to refer to the server.

    So in case something happened to the hardware on the production machine you can just mofify the alias to point to the second machine and then take your time restoring the database on the first server. Do not forget master and MSDB databases in case you have many logins, jobs and DTS packages.

    Regards,Yelena Varsha

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

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