Error: All files must be specified for database snapshot creation. Missing the file "AdventureWorks2017".

Question

Hello,
I'm trying to create a snapshot of my sample database AdventureWorks2019.
I'm using the following code:

use AdventureWorks2019
CREATE DATABASE AdventureWorks_2030 ON
( NAME = 'AdventureWorks', FILENAME = '/srv/shared/snapshots/AdventureWorks_2030.ss' )
AS SNAPSHOT OF AdventureWorks2019;
GO

Now, I get an error (All files must be specified for database snapshot creation. Missing the file "AdventureWorks2017".) unless I don't change NAME to AdventureWorks2017... This really puzzles me, shouldn't it be an arbitrary name for the generated snapshot? the source AdventureWorks2019 name is AdventureWorks2019 also in the properties, so I don't get where this AdventureWorks2017 is coming from

Answer 1

When you create a snapshot, the "NAME" is the source database file LOGICAL name. You can see these names by running:

select *  
from sys.sysfiles  
  

For AdventureWorks2019, they apparently forgot to rename the logical name of the file. It is "AdventureWorks2017". So your command should be:

CREATE DATABASE AdventureWorks_2030 ON  
( NAME = 'AdventureWorks2017', FILENAME = '/srv/shared/snapshots/AdventureWorks_2030.ss' )  
AS SNAPSHOT OF AdventureWorks2019;  

Also see:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql?view=sql-server-ver15#TsqlProcedure