can noit create database snapshot in sql-server 2019

Question

in my work ,.

I use sql-server 2019 and create a database named DB_TeachingMS , and I want create a database snapshot but I meet this problem, It always tiped me , must need all files to create a snapshot of database;

I was very curios of this problem;

I immeditely need your help ,thanks

Answer 1

What it says. Well, I don't know how clear the Chinese version is but, the English message is All files must be specified for database snapshot creation. Missing the file "%ls".

This suggests one of two things:

1. The database has more than one data file.
2. You have misspelled the name of the single file.

I notice that the name of the file that is missing is the same as the database name. This makes me lean towards the second option. That is, rather than DB_TeachingMS1, just enter DB_TeachingMS.

Answer 2

Hi @xyh

Try this:

CREATE DATABASE DB_TeachingMS_Snapshots 
ON ( NAME = DB_TeachingMS,
     FILENAME = 'C:\sqlsnapshot\DB_TeachingMS_Snapshots.ss')  
AS SNAPSHOT OF DB_TeachingMS; 

Refer to this article for more details: Create a Database Snapshot (Transact-SQL).

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

	Declare @sourcedb as nvarchar(255)=N'DB_TeachingMS' --your database name	
	Declare @snap_db as nvarchar(255)=@sourcedb+'_ss' 
    Declare @logic_name as nvarchar(128)
	Declare @snapPath as nvarchar(2000)

--Retrieve variable values
SELECT @logic_name=name, @snapPath=replace(physical_name,@sourcedb,@snap_db+'.snap') 
FROM sys.master_files
WHERE database_id = DB_ID(@sourcedb) and file_id=1


declare @sql nvarchar(4000)
Set @sql=N'CREATE DATABASE '+@sourcedb +'_Snapshot'+format(getdate(),'yyyyMMddhhmm')
+' ON (NAME = '+@logic_name+', FILENAME = '''+@snapPath+''')
AS SNAPSHOT OF '+ @sourcedb


exec(@sql)

Go