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

Eirinn 41 Reputation points
2021-05-27T10:25:47.307+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,514 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,706 Reputation points
    2021-05-27T13:47:47.973+00:00

    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

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful