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


Refresh Database Snapshot on SQL Server

SQL Server database snapshots are readonly storing data which was on source database at the creation time of snapshot. To refresh snapshot database on SQL Server with changed data on source, database administrators have to drop and re-create the snapshot database.

A database snapshot created on a SQL Server instance shows the data view at the creation instant and will not be affected by the changes on the source database after snapshot creation. This means SQL Server database snapshots are not updated by changes made on the source database after the snapshot itself is created.


Create Database Snapshot

Let's make a step by step sample for our SQL tutorial.
First create a database on your SQL Server instance.
Then, we will create the database snapshot of our source database.

While snapshot creation on SQL Server, developers or administrators should take all data files of the source database.
I mean, in database create command, filename for each corresponding mdf and for all ndf data files should be set.
.mdf for Primary data file, .ndf for each Secondary data files.

-- create source database on SQL Server
Create Database [Kodyaz Database]

-- create database snapshot of SQL Server sample database
CREATE DATABASE [Kodyaz Database SS]
ON (
NAME = [Kodyaz Database],
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Kodyaz Database.ss'
)
AS SNAPSHOT OF [Kodyaz Database];
GO

After database snapshot is created, it can be seen in the SQL Server Database Snapshots node on Object Explorer.

database snapshots on SQL Server Object Explorer

After database snapshot creation, if you update a table data on source database, you will not see the change on snapshot.
Snapshot database is readonly, it is not possible to modify data stored in snapshot database.
The only way to reflect changes since the snapshot creation, is to drop database snapshot and then to create it once more.


Refresh Database Snapshot using Drop and Create

Assume that you are a database administrator or a SQL developer, who works on a readonly database snapshot instead of the transactional source database. Perhaps you use the SQL Server database snapshot for reporting purposes and want to refresh database snapshot to reflect data changes on the source database to the snapshot database wo work with most recent data.

So how can you refresh a database snaphot to enable data changes made since snapshot creation?

In such cases, the only solution to refresh a database snapshot is to drop existing SQL db snapshot and recreate it once more with the same properties.

-- drop snapshot database
Drop Database [Kodyaz Database SS]

-- re-create to refresh sbapshot database
CREATE DATABASE [Kodyaz Database SS]
ON (
NAME = [Kodyaz Database],
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Kodyaz Database.ss'
)
AS SNAPSHOT OF [Kodyaz Database];
GO

In short, to refresh database snapshot, drop and re-create database snapshot on SQL Server with the same sapshot name.

drop and create database snapshots on SQL Server


Refresh Database Snapshot Automatically using Snapshot Name

How can we refresh database snapshots automatically by dropping and re-creating of a database snaphot using the database snapshot name?

Please check following dynamic SQL script which drops the given snapshot and re-create the database snapshot again.

Declare @DatabaseSnapshot sysname
Set @DatabaseSnapshot = 'DatabaseSnapshotName'

Declare @SourceDatabase sysname

Select @SourceDatabase = s.name
From sys.databases ss -- snapshot database
Inner Join sys.databases s -- source database
 On ss.source_database_id = s.database_id
Where ss.name = @DatabaseSnapshot

If @SourceDatabase is not null
Begin
 declare @sql nvarchar(max) = ''
 set @sql = N'Drop DATABASE [' + @DatabaseSnapshot + ']'
 exec sp_executesql @sql
End

set @sql = ''
;with cte as (
 select
  name, physical_name, ROW_NUMBER() over (order by create_lsn) rn
 from sys.master_files
 where database_id in (
  select database_id
  from sys.databases
  where name = @SourceDatabase
 )
 and type = 0 -- ROWS
)
select
 @sql = @sql + '(NAME = [' + name + '], FILENAME =''' + physical_name + '.ss''),'
from cte

set @sql = SUBSTRING(@sql,1,len(@sql)-1)

set @sql = N'
 CREATE DATABASE [' + @DatabaseSnapshot +'] ON
 ' + @sql + '
 AS SNAPSHOT OF [' + @SourceDatabase + '];'
--print @sql
exec sp_executesql @sql
go

In above dynamic SQL script, SQL programmers can see that I build SQL statement in @sql string variable.
I use sp_ExecuteSQL to evaluate SQL command twice; once for dropping database snapshot and once for snapshot creation.


SQL Server Stored Procedure to Recreate Database Snapshots

SQL programmers can go further by creating a stored procedure to drop and recreate a database snaphot to refresh snapshot database to reflect data changes on its source.

Create Procedure spKodyaz_Refresh_Database_Snapshot(
 @DatabaseSnapshot sysname
)
as

Declare @SourceDatabase sysname

Select @SourceDatabase = s.name
From sys.databases ss -- snapshot database
Inner Join sys.databases s -- source database
 On ss.source_database_id = s.database_id
Where ss.name = @DatabaseSnapshot

If @SourceDatabase is not null
Begin
 declare @sql nvarchar(max) = ''
 set @sql = N'Drop DATABASE [' + @DatabaseSnapshot + ']'
 exec sp_executesql @sql
End

set @sql = ''
;with cte as (
 select
  name, physical_name, ROW_NUMBER() over (order by create_lsn) rn
 from sys.master_files
 where database_id in (
  select database_id
  from sys.databases
  where name = @SourceDatabase
 )
 and type = 0 -- ROWS
)
select
 @sql = @sql + '(NAME = [' + name + '], FILENAME =''' + physical_name + '.ss''),'
from cte

set @sql = SUBSTRING(@sql,1,len(@sql)-1)

set @sql = N'
 CREATE DATABASE [' + @DatabaseSnapshot +'] ON
 ' + @sql + '
 AS SNAPSHOT OF [' + @SourceDatabase + '];'
--print @sql
exec sp_executesql @sql
go

Above SQL stored procedure can be used to refresh a database snapshot on SQL Server as follows:

refresh database snapshot using SQL Server stored procedure

-- pass database snapshot name as input parameter to stored procedure
EXEC spKodyaz_Refresh_Database_Snapshot 'Kodyaz Database SS'

download SQL script Download source codes for above sample stored procedure spKodyaz_Refresh_Database_Snapshot to refresh database snapshot using referred download link.


Re-Create Database Snapshot of a SQL Server Database

If you want to drop and re-create snapshot database of a specific SQL Server database, how can we automatically manage this?
Let's first create the SQL script which does following tasks in order:
Identify the snapshot database name for given SQL Server database,
Drop database snapshot,
Re-create database snapshot

Declare @SourceDatabase sysname = 'Kodyaz Database'
Declare @DatabaseSnapshot sysname

If (
 Select COUNT(*)
 From sys.databases ss -- snapshot database
 Inner Join sys.databases s -- source database
  On ss.source_database_id = s.database_id
 Where s.name = @SourceDatabase
) > 1
 print 'Source database has more than one snapshots. Snapshot name required to drop & re-create!'
else
begin

Select @DatabaseSnapshot = ss.name
From sys.databases ss -- snapshot database
Inner Join sys.databases s -- source database
 On ss.source_database_id = s.database_id
Where s.name = @SourceDatabase

If @DatabaseSnapshot is not null
Begin
 declare @sql nvarchar(max) = ''
 set @sql = N'Drop DATABASE [' + @DatabaseSnapshot + ']'
 exec sp_executesql @sql
End
Else
 set @DatabaseSnapshot = @SourceDatabase + '_SS'

set @sql = ''
;with cte as (
 select
  name, physical_name, ROW_NUMBER() over (order by create_lsn) rn
 from sys.master_files
 where database_id in (
  select database_id
  from sys.databases
  where name = @SourceDatabase
 )
 and type = 0 -- ROWS
)
select
 @sql = @sql + '(NAME = [' + name + '], FILENAME =''' + physical_name + '.ss''),'
from cte

set @sql = SUBSTRING(@sql,1,len(@sql)-1)

set @sql = N'
 CREATE DATABASE [' + @DatabaseSnapshot +'] ON
 ' + @sql + '
 AS SNAPSHOT OF [' + @SourceDatabase + '];'
--print @sql
exec sp_executesql @sql

end
go

Above SQL script first checks if more than one snapshots of the target SQL Server database is created or not.
If there are at least two database snapshots are created for the given SQL database, the code exists with warning.

If there is only one snapshot no snapshots created for the target SQL Server database, the script drop database snapshot first.

As the following step, the database snapshot is created again, with the datafile names of the original source database.

Here is how SQL professionals can use above stored procedure to refresh snapshot created for specific database TestDB.

exec spKodyaz_Create_Database_Snapshot_for 'TestDB'

If you want to use above SQL codes in your productive SQL Server instances, I advice SQL developers or database administrators to test below stored procedure on your development and test systems first.

database snapshot script download Download stored procedure spKodyaz_Create_Database_Snapshot_for to refresh database snapshot of a specific SQL Server database using given download link.


Create and Refresh Snapshots for all Databases

One last note for all database administrators, if you require to create database snapshots for all databases existing on your SQL Server instance and frequently drop and recreate these snapshots to refresh data, please check SQL tutorial Create Database Snapshot for all Databases on SQL Server. In this SQL Server tutorial, I have shared a SQL script which executes spKodyaz_Create_Database_Snapshot_for stored procedure for all databases using the sp_Msforeachdb undocumented stored procedure.



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.