File does not exist in database - SQL Server Snapshot

Question

I am attempting to create a database snapshot with SQL Server 2008 R2 using the following T-SQL code.

CREATE DATABASE SNAP_myDB_0900
ON
 (NAME = myDB, FILENAME = 'C:\myDB_0900.SNAP')
AS SNAPSHOT OF myDB

I get the following error:

The file 'myDB' does not exist in database 'myDB'

This code works with other databases in the same instance but not this one. I have double checked the file name and it is correct.

Why am I getting this error?

Does the server have access to the direct C:\ that the database lives on? The C:\ on your computer is not the same C:\ referenced in the statement. — TTeeple
– TTeeple, Commented Sep 14, 2015 at 17:31
@TTeeple the server does have access, as this code works with other databases on the server instance. — Telos
– Telos, Commented Sep 14, 2015 at 19:08

Thomas Stringer · Accepted Answer · 2015-09-14 17:54:18Z

7

Verify the database file name that you are trying to create the snapshot based off of:

select name, physical_name
from myDB.sys.database_files;

The NAME you give you snapshot file(s) needs to match the source database file name.

In other words, if myDB's data file has a name of datafile1, then you will have to use ... NAME = 'datafile1' ... when creating your snapshot.

answered Sep 14, 2015 at 17:31

Thomas Stringer

5,9123 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I was using the physical_name that resulted in the error, it is working now with the name in the "name" column. Thanks much!