question

Eirinn-0836 avatar image
0 Votes"
Eirinn-0836 asked Eirinn-0836 commented

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

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered Eirinn-0836 commented

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://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql?view=sql-server-ver15#TsqlProcedure

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks!
So I reckon the name stated in the properties (accessible by DB_NAME) is not the logical name?

0 Votes 0 ·

Snapshots generate snapshots from files associated with the database (using the logical name), not the database itself.

In your example, you have database name of "AdventureWorks2019". The database contains a "logical" file named "AdventureWorks2017". The snapshot requires all "logical" files to be snapshotted. In this case, there is only 1 logical file to snapshot. However, there can be many in large databases.

1 Vote 1 ·

Crystal clear! Many thanks!

0 Votes 0 ·