Creating SQL Server Database Snapshot – Error 5014

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:

error_5014_database_snapshot

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')

database_files_logical_name

From SQL Server Management Studio (SSMS) you can see the database logical file names:

Right click on a database -> Properties -> Files:

database_properties_files_logical_name

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

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">