Share via

What is the best scenario to back up sensitive database in SQL Server?

Farshad Valizade 501 Reputation points
2024-01-12T04:32:21.0333333+00:00

I have read many topics here and out of here and I didn't understand what to do at the end. So I have opened this topics. I have a sensitive database that users enter data from 6:30 am until 16:30 pm and this data shouldn't be lost because they are very important for the company. So I make a 3 maintenance plan for my database:

  1. Full Backup Every day at 16:30 pm
  2. Differential every hour
  3. Transactional Log every day at 16:45pm
  • Question 1: If this kind of Scenario is right?
  • Question 2: When I get a full back up then Transactional Log is needed?
  • Question 3: Get a Differential backup is suitable for every hour or not?
SQL Server | Other
Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2024-01-12T22:46:45.6533333+00:00

    I would say that your backup plan is sort of odd. You absolute need to run T-log backups when you have full backups, yes. Differential backups on the other hand, maybe, but not an absolute must.

    To set up a backup plan, you first need to have a restore plan. That is, if there is a disaster, what are your RPO and your RTO?

    • RPO - Recovery Point Objects - how much data can you lose? The last 15 mintues? The last minute? Nothing at all?
    • RTO - Recovery Time Objectvie - How long downtime can you accept? One hour? The full day?

    These are questions that your business may have to answer. When you give them the costs, the may backtrack on their original requirements.

    When there is a disaster, the normal procedure is:

    • Restore the most recent clean full backup. (If there has been corruption going on in the system, that may be not be your very most recent backup.)
    • Restore the most recent differential backup taken after that full backup - if you have one.
    • Apply transaction log backups taken since the last differential.

    If you are taking daily full backups, I don't see the point in differentials. I think differentials are useful if you have a database that is large, so you only want to run a full backup on weekends, and then take daily differentials so that you don't have to apply t-log backups all through the day. But the exact tuning, of this you can only find out by testing the restore operation to verify that you meet your RTO.

    How often to take T-log backups depend on your RPO, but I would say every 15 minutes is good bet.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Hugo Barona 416 Reputation points MVP
    2024-01-12T09:06:05.24+00:00

    Hi Farshad,

    Your backup strategy seems quite robust, involving full backups, differential backups, and transactional log backups.

    Question 1: Is This Kind of Scenario Right?

    1. Full Backup Daily at 16:30 PM: This is a good practice as it ensures you have a complete copy of your database at the end of each working day. It captures all the data in the database up to that point.
    2. Differential Backup Every Hour: Differential backups are backups of all changes made since the last full backup. Doing this hourly means, you're limiting the amount of data that could be lost in case of a failure to, at most, one hour's worth of work.
    3. Transactional Log Backup Daily at 16:45 PM: Transaction log backups are crucial for databases that are in full recovery or have a bulk-logged recovery model. They allow you to restore a database to a specific point in time and are essential for maintaining the log chain.

    Question 2: Is Transactional Log Backup Needed if You Have a Full Backup?

    Yes, transactional log backups are still needed even if you have a full backup. Here's why:

    • Point-in-Time Recovery: Transactional log backups are essential for point-in-time recovery. They allow you to restore the database to any specific time, which can be critical if you need to recover from an error or a data corruption issue that occurred after the last full backup.
    • Log Chain Maintenance: In full recovery models, transaction log backups are necessary to maintain the log chain integrity. Without regular log backups, the transaction log can grow indefinitely, which can lead to space issues and impact database performance.

    Question 3: Is Differential Backup Suitable Every Hour?

    Whether hourly differential backups are suitable depends on a few factors:

    • Database Size and Activity: If your database is highly active and undergoes significant changes every hour, then hourly differential backups make sense.
    • Recovery Objectives: Your Recovery Point Objective (RPO) and Recovery Time Objective (RTO) play a crucial role in determining backup frequency. If your RPO is very stringent (i.e., you can't afford to lose more than an hour's worth of data), then hourly differentials are appropriate.
    • Resource Utilization: Frequent backups can consume resources. Ensure that your backup strategy doesn't negatively impact database performance during peak hours.
    • Storage Constraints: More frequent backups mean more storage usage. Ensure you have enough storage capacity and that you're managing old backups effectively.

    Let me know if this answers your questions or if you have any additional questions.

    If this answers your questions, please mark it as answered.

    Thank you.

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.