SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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"

create snapshot for all databases on SQL Server using sp_Msforeachdb

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.

snapshots for all existing databases on SQL Server
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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.