The syntax for creating SQL Server database snapshot is:
CREATE DATABASE database_snapshot_name
ON
(
NAME = logical_file_name,
FILENAME = 'os_file_name'
) [ ,...n ]
AS SNAPSHOT OF source_database_name
[;]
You need to specify the logical name of every data file of the source database.
If you try to create a database snapshot and you provide a wrong logical name of a database file:
CREATE DATABASE AdventureWorks_dbss ON
( NAME = AdventureWorks, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks_data.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
...you will get the following error:
Msg 5014, Level 16, State 3, Line 1
The file 'AdventureWorks' does not exist in database 'AdventureWorks'.
To list the database files and their logical names run this query:
SELECT name AS logical_name, type_desc, physical_name from sys.master_files
WHERE database_id = db_id('Database_Name')
From SQL Server Management Studio (SSMS) you can see the database logical file names:
Right click on a database -> Properties -> Files:
Correct the logical file names, and execute the code again:
CREATE DATABASE AdventureWorks_dbss ON
( NAME = AdventureWorks2008R2_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks_data.ss' )
AS SNAPSHOT OF AdventureWorks;
GO