CODEWITHSUNDEEP
sqlazuresnapshot
SBFrancies
SBFrancies

Reputation: 4250

Is it possible to create database snapshots on an Azure SQL virtual machine?

I was wondering if it is possible to create database snapshots on an Azure SQL virtual machine. The following query:

CREATE DATABASE <DB_Name>_Snapshot on (
    Name = <DB_Name>,
    filename = '\<DB_Name>.ss'
) as snapshot of <DB_Name>;

Gives this error:

A database snapshot cannot be created because it failed to start.

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file '<DB_Name>.ss'

Normally this is a permissions issue which can be resolved by giving the SQL service account the relevant access but I can't work out if it can be done on an SQL virtual machine.

Upvotes: 0

Views: 483

Answers (1)

Leon Yue
Leon Yue

Reputation: 16431

As I searched and found that Azure SQL VM support create database snapshots. It depends on you SQL Server version. Ref here: Editions and supported features of SQL Server 2016

Others get the same error which all caused by you are getting a permissions error. The account which is running the SQL Server doesn't have the needed rights on the folder which will contain the database files.

Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

You ref these blogs:

  1. https://blog.sqlauthority.com/2018/08/25/sql-server-fix-create-file-encountered-operating-system-error-5-access-is-denied/
  2. https://dba.stackexchange.com/questions/22250/create-file-encountered-operating-system-error-5-access-is-denied
  3. https://knowledge.autodesk.com/support/vault-products/troubleshooting/caas/sfdcarticles/sfdcarticles/CREATE-FILE-encountered-operating-system-error-5-Access-is-denied-when-attaching-databases-in-Vault.html
  4. https://sqlcan.com/2010/04/23/create-file-encountered-operating-system-error-5access-is-denied/

These links can give the guides to solve the problem.

Upvotes: 1

Related Questions