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.
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.
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:
-- pass database snapshot name as input parameter to stored procedure
EXEC spKodyaz_Refresh_Database_Snapshot 'Kodyaz Database SS'
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.
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.