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.
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.
- Add the SQL Server service (domain) account to the local Administrators group and restart the SQL service.
- Grant the local
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
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.