Create Snapshot for Databases with Multiple Data Files
To create snapshot for databases with multiple data files could be difficult for SQL developer and SQL Server database administrators at first. This SQL Server tutorial shows how to create database snapshot for databases which have primary data file (.mdf) and secondary data files (.ndf files) too.
I will also share a SQL script which can be used to create database snapshot for allowed SQL Server databases (databases except in-memory databases, master and other system databases like model, tempdb).
If SQL programmer wants to create the database snapshot manually using SQL Server Management Studio which is the better way, he or she can display the properties of the database first.
How to display database properties in SQL Server Management Studio
Then switch to Files tab.
How to display database files of a SQL Server database in SSMS
As seen in above screenshot, this tutorial's sample database has 3 database files.
One primary data file (.mdf file) named "kodyaz" with rows data type,
One secondary data file (.ndf file) named "kodyaz2" with rows data type,
And finally the log file (.ldf file) named "kodyaz_log" with log type
Please note that, for a SQL Server database primary data file (.mdf) can only be one for that specific database. On the other hand, SQL database administrators could create multiple secondary data files (.ndf files) for the same database.
While database snapshot creation, in "Create Database" command syntax we have to define a corresponding file for each rows data type file.
Let's complete the sample
Before begin, just to make things in T-SQL, query sys.database_files system view on target database as follows:
select name, type_desc, physical_name from sys.database_files
Now SQL programmer can use the following syntax to create database snapshot on SQL Server using T-SQL command
CREATE DATABASE [SnapshotDatabaseName]
ON
(NAME = [primary_data_file_name_of_source],
FILENAME ='SQLServer_Data_folder\new_data_file_name.mdf'),
(NAME = [secondary_data_file_name_of_source],
FILENAME ='SQLServer_Data_folder\new_data_file_name.ndf')
AS SNAPSHOT OF [SourceDatabaseName];
GO
I implemented above database snapshot creation syntax on sample database as follows
First of all,
Name snapshot for your database,
Then while keeping the name for each file, change the data file (you can add _SS or _snapshot, etc) file without changing the path.
Keep each file configuration between parentheses with comma between two
CREATE DATABASE [kodyaz SnapshotDB]
ON
(NAME = [kodyaz], FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\kodyaz_snapshot.mdf'),
(NAME = [kodyaz2], FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\kodyaz_snapshot.ndf')
AS SNAPSHOT OF [kodyaz];
GO
This is all for manual database snapshot creation SQL syntax for programmers and database administrators.
It is also possible to create database snapshot by SQL stored procedure or automatically by running SQL script given at referred SQL Server tutorial.
SQL Server database administrators can also review my SQL tutorial Create Database Snapshot for all Databases for details on automatically snapshot creation on a SQL Server instance.