SQL Server 2008 R2 – Cannot create database snapshot

ntfssnapshotsql-server-2008-r2windows-server-2008-r2

Server: Windows Server 2008 R2 X64 Enterprise

SQL: SQL Server 2008 R2 Enterprise X64

I have a default SQL Server instance, the SQL Server service account is running as a domain user.

I am trying to create a database snapshot in the directory where the mdf files are stored. The T-SQL syntax is correct. The file system is NTFS.

The error message I get is: 

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.

Msg 5119, Level 16, State 1, Line 1
Cannot make the file "e:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.ss" a  sparse file. Make sure the file system supports sparse files.

The local SQLServerMSSQLUser$db$MSSQLSERVER group has Full Control permission on the folder where I am trying to create the snapshot.

I can fix the problem in two ways, neither of which are suitable.

  1. Add the SQL Server service (domain) account to the local Administrators group and restart the SQL service.
  2. Grant the local SQLServerMSSQLUser$db$MSSQLSERVER group Full control on E:\

I have tried to change the owner of the DATA directory to SQLServerMSSQLUser$db$MSSQLSERVER to no avail. I have no issue creating a new database

Why can I not create a snapshot by giving permission only on the DATA folder?

Update 23/09/2010:

I have tried mrdenny's suggestion with no luck (but learned something new in the process), I suspect the problem may be due to the fact that the domain is a windows 2000 domain running in mixed mode. I had to install hotfix KB976494 for Server 2008 R2, as the SQL Server 2008 R2 installer would not verify the service account correctly with the domain.

I noticed that Server 2000 isn't a supported operating system for SQL 2008 R2 but cannot find anything that would suggest it shouldn't work in a 2000 domain.

I dis-joined the test server from the domain and changed the service accounts to the local service account and I still have the same issue. I will try to re-install the server without joining the domain and without the hotfix and see if the issue persists.

Best Answer

That's a low level NTFS error. It that file the one that you are trying to create for the snapshot? Does the SQL Server have the instant file initialization right? (Its about the only thing I can think of that might cause this.)

Related Topic