What Should be...?

  • Dear friends,

    Using Always On in our corporate site, we have 3 nodes deployed, our main database backup is based on images of that virtual  machines, but constantly I have databases requesting transaction log backups, what role should have a normal backup of transaction log files vs virtual machines images backup, our management team has choosen this option as the only backup policy.

    Thanks for your support

     

  • There is a huge difference between image back-up and a database back-up. It's a long story to explain everything of a database back-up, so I suggest you first read articles/blogs to extend your knowledge (like: https://www.sqlservercentral.com/stairways/stairway-to-transaction-log-management-in-sql-server and https://www.sqlshack.com/understanding-sql-server-backup-types/ )

    Short answer: it is NOT correct to only use image back-up.

    - Images are like a snapshot and can only be used to revert to that single moment when the image was created. This type of back-up doesn't take database integrity into account and a revert could lead to a corrupt database. It will also NOT limit the size of the database LOG file, resulting in an ever increasing file and eventually a full disk.

    - A good database back-up strategy can be used to revert the database to any moment in the past. Regular LOG back-ups are required for databases in FULL recovery model (as they are with Always On). This will keep LOG file size within limits.

    • This reply was modified 6 months ago by  HanShi.
    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • So, have you and the business worked out your Recovery Time Objective and Recovery Point Objective? If not, go do that first. If so, test this method on a restore. It's that simple. Have them demonstrate that they can meet the RTO & RPO with a restore. If they can't, chuck that thing to the curb and start building out a backup strategy that can. It's that easy. Never argue. Just say, cool, sounds great, show me a restore that meets our business requirements. Now show me another. Do they pass? Awesome. If they don't, the statement is simple, "We're not using that."

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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