question

SQLDBA123 avatar image
SQLDBA123 asked

Create snapshot database error on remote server

**Environment:** OS: Windows 2008 R2 SQL Server : 2008 R2 EE Shared NAS storage: NTFS (\\RemoteServerName\SharedFolderName) Permisisons on Shared folder: Full control Source DB model: Full recovery. **Snapshot DB Script:** CREATE DATABASE TestDB_Snapshot1 ON ( NAME = TestDB_data, FILENAME ='\\RemoteServerName\SharedFolderName\TestDB_Snapshot1.ss') AS SNAPSHOT OF TestDB GO Error: Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start. Msg 5119, Level 16, State 2, Line 1 Cannot make the file "\\RemoteServerName\SharedFilderName\TestDB_Snapshot1.ss" a sparse file. Make sure the file system supports sparse files. Questions 1. Can't we create snapshot database on remote server location if NTFS filesystem? Seen in below MS KL limitations, FAT32 not support snapshot databases. But our storage is NTFS filesystem(NAS storage). http://msdn.microsoft.com/en-us/library/ms175158.aspx#Prerequisites 2. Any suggestion to create snapshot on NAS storage area wihtout any sparse files errors? **Note:** I am able to create snapshot database if it is local disk on the server and not able to create if it remote server shared location Thanks in advance.
sql-server-2008-r2snapshotwindows-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
I would confirm the following are correct. 1. File system is truly NTFS and not FAT32 2. Log on to server and test creation locally. 3. Create a different folder and test creating Snapshot in new folder.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLDBA123 avatar image
SQLDBA123 answered
It did not work on NAS storage. The project design changed and creatingthe snapshot on local disk.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.