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