Create Database Snapshot for all Databases on SQL Server
This SQL Server tutorial shares SQL script to create database snapshots for all databases on a SQL Server instance for database administrators. If you require to create snapshots for each database created on a given SQL Server, you can use SQL script which use sp_Msforeachdb undocumented stored procedure with an other stored procedure which is used to create database snaphot for a specific database.
Database snapshots are created with existing data dictionary or meta data and transactional data of the source database at the time of snapshot creation. Snapshot of a database is readonly which means SQL users can not change data or data structure on the snapshot. Besides this, all changes made on source database will not be available on the snapshot database.
On the other hand, since database snapshots are generally used for reporting purposes, SQL Server data professionals frequently require to refresh database snaphots by dropping existing snapshot and recreating it with the same name. In general, a SQL Server job is created to fulfill this task, refreshing database snaphots.
Following section will be sharing a small script build with sp_Msforeachdb, running once for each database on the SQL Server and creates a snapshot of that SQL database.
Create Database Snapshot for all Databases on SQL Server Instance
SQL programmers can execute a script on each database existing on the current SQL Server instance using undocumented stored procedure sp_Msforeachdb.
If we use sp_Msforeachdb with spKodyaz_Create_Database_Snapshot_for, it is possible to create database snaphot for each SQL Server database on the instance using single command.
EXEC sp_Msforeachdb "
begin try
exec spKodyaz_Create_Database_Snapshot_for '?'
end try
begin catch
print '?'
end catch"
Here is the result of the above script execution on my local SQL Server instance.
You can see a database snapshot corresponding to SQL database which is created on the target SQL Server instance.
Screenshot showing all database snapshots in SQL Server Management Studio Object Explorer window
Please note that, it is not possible to create snapshot for system databases including master, tempdb and model databases.
Also if target database is a memory optimized database, we cannot create snapshot for it.
Also database snapshot on another database snapshot can not be created.
SQL Server Tutorials on sp_MSForEachDB Stored Procedure
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access