SQL Server 2008 R2 - Cannot create database snapshot
Posted
by
Chris Diver
on Server Fault
See other posts from Server Fault
or by Chris Diver
Published on 2010-09-21T15:05:02Z
Indexed on
2012/12/15
11:08 UTC
Read the original article
Hit count: 408
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.
- Add the SQL Server service (domain) account to the local Administrators group and restart the SQL service.
- Grant the local
SQLServerMSSQLUser$db$MSSQLSERVER
groupFull control
onE:\
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.
© Server Fault or respective owner