Create Snapshot for Databases with Multiple Data Files in sqlserver

sql, sql-server

Solution

CREATE DATABASE databaseName_report_snap ON
(Name ='databaseName',
FileName='E:\SqlData\databaseName_report.snp'),
(Name ='databaseName_EX',
FileName='E:\SqlData\databaseName_report_ext.snp')
AS SNAPSHOT OF databaseName;

Problem

How to create database snapshot for databases which have primary data file (.mdf) and secondary data files (.ndf files) too. Throws exception Missing the file databaseName_EX when I create it. The database file includes databaseName_EX.ndf and databaseName.mdf ``` CREATE DATABASE databaseName_report_snap ON (Name ='databaseNameJ', FileName='E:\SqlData\databaseName_report.snp') AS SNAPSHOT OF databaseName; ```

Original source