none
Backup Strategy Question for SQLSErver 2008 Cluster RRS feed

  • Question

  • Hello,

    On my SQLServer 208 cluster I have a H drive on each node which is on LocalDisk. I am currently using Maintenance plans to perform backup to the local H drive on the server.

    I have 3 questions:

    1. All my dbs are full recovery models and I am planning on taking hourly TLOG backups. Does it make sense to create a seperate maintenance plan just to backup TLOGs?

    2. I am planning on running a full backup of the dbs at 8pm. If the full backup kicks off at 8pm and the tlog backup is scheduled to run at 8 as well will tlog backup wait for the full backup to complete?

    3, I am using 1 maintenance plan to backup 300 database. What is the best practice?
    Friday, March 12, 2010 12:19 AM

Answers

  • Hello,

    1. This is personal preference in my opinion. You can have one massie maintenance plan for everything or have individual ones, maybe ones grouped by application. I think this is more of your choice and how easy you want to make it on yourself to find and read errors, as well as keeping track of change control, etc.

    2. It depends on the order of which the backups started, but I would advise you to change the times. You will stress your IO channels if you have all 300 databases backup at the same time, especially with their associated transaction logs.

    3. I think this belogns with question number 1. This is personal preference, but I can tell you that if I had to check your environment I wouldn't want to see 300 plans, then again I wouldn't want to just see 1 either unless it's very straight forward and easy to read.

    Hope this helps,
    Sean
    Friday, March 12, 2010 11:12 AM
    Answerer
  • Hi,
    If you are backing up your database to local H: drive, what will happen if that server goes down?  Presumably you won't be able to get at the backups from the second node.  This might put you at risk if you have a problem whilst resolving the issue with your primary.
    Cheers, Andy

    Friday, March 12, 2010 11:37 AM
  • Creating a Maintinance Plan will not resolve ur concern of loosing ur data as maintinance plan will perform backup of database one by one (in a loop) .This raises a risk of data loss in case of crash.



    Seggrigate the Databases in terms of Mission critical (No DataLoss), Crirtical (No DataLoss) and General (ready to bear loss).

    Create 5 to 6 maintinance plan and backup the database as Mission Critical --- Crirtical ---- General


    Hope this will help

    suggestion : as u have huge amount of database , use litespeed as it will be very fast in term of full backup.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Friday, March 12, 2010 2:26 PM

All replies

  • Hello,

    1. This is personal preference in my opinion. You can have one massie maintenance plan for everything or have individual ones, maybe ones grouped by application. I think this is more of your choice and how easy you want to make it on yourself to find and read errors, as well as keeping track of change control, etc.

    2. It depends on the order of which the backups started, but I would advise you to change the times. You will stress your IO channels if you have all 300 databases backup at the same time, especially with their associated transaction logs.

    3. I think this belogns with question number 1. This is personal preference, but I can tell you that if I had to check your environment I wouldn't want to see 300 plans, then again I wouldn't want to just see 1 either unless it's very straight forward and easy to read.

    Hope this helps,
    Sean
    Friday, March 12, 2010 11:12 AM
    Answerer
  • Hi,
    If you are backing up your database to local H: drive, what will happen if that server goes down?  Presumably you won't be able to get at the backups from the second node.  This might put you at risk if you have a problem whilst resolving the issue with your primary.
    Cheers, Andy

    Friday, March 12, 2010 11:37 AM
  • Creating a Maintinance Plan will not resolve ur concern of loosing ur data as maintinance plan will perform backup of database one by one (in a loop) .This raises a risk of data loss in case of crash.



    Seggrigate the Databases in terms of Mission critical (No DataLoss), Crirtical (No DataLoss) and General (ready to bear loss).

    Create 5 to 6 maintinance plan and backup the database as Mission Critical --- Crirtical ---- General


    Hope this will help

    suggestion : as u have huge amount of database , use litespeed as it will be very fast in term of full backup.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Friday, March 12, 2010 2:26 PM