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?

Answer 1

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.