I have SQL Server running on a virtual machine (VM). Weekly VM backup is done. My question is when we backup the VM, does it locks down the SQL Server or the databases residing in that SQL Server? I understand VM backup is at the OS level but still want to confirm since I am in middle of investigating an issue and I am not a database expert.

  • Are you taking a VM snapshot and backing up that? Or just copying the VM's datafiles while the VM is running? Which VM platform are you running on? – Colin 't Hart Apr 28 '15 at 11:49
  • @Colin, we take VM full backup, no snapshot. We are using VM ESX – Zakir HC Apr 28 '15 at 12:04
  • 3
    See blogs.sqlsentry.com/aaronbertrand/bad-habits-file-backups and more importantly m.sqlmag.com/blog/… ... whatever you are relying on for OS-level backups is no substitute for proper native SQL Server backups. IMHO you need to manage those separately. – Aaron Bertrand Apr 28 '15 at 12:23

If the "SQL Server VSS Writer" service is running IO on the database files will be "frozen" while a snapshot is made of the databases and the database state (full database backup) will be included with the VM backup. This will not lock down the SQL server but will in some cases make it slower.

If the service is not running the database files will not be included in the backup and you have to use other means to backup them. This needs to be discussed with your DBA in many cases having full backups taken with the VM backups breaks the backup chain the DBA is maintaining and you should definitely make those backups more often than weekly and backup your transaction log as well. The VM backup (snapshot) can also affect performance of the SQL Server even though it's not making a snapshot of the database files.

  • so that means VM backup does not locks the database but can make it slow? – Zakir HC Apr 28 '15 at 11:24
  • 1
    Yes, the backups can be pretty intensive on the VM datafiles and SQL server is I/O bound – Spörri Apr 28 '15 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.