My Technical Notes

Monday, 21 March 2016

Creating and Restoring a Database Snapshot

Creating a Database Snapshot

Create a Snapshot the Simple Way

Given a database `Ψ`, we create a snapshot `Ψ_SS` by using:


CREATE DATABASE Ψ_SS ON
( NAME = Ψ, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\Ψ_SS.ss' )
AS SNAPSHOT OF Ψ;
GO
Create a Snapshot the Advanced Way

Apparently, there are many files that a database comprises of, therefore they all need to be listed when the snapshot is created. The following database script, by Tom Phillips from MSDN forum, will create the snapshot automatically, taking into account potentially multiple database files:


-- MUST BE RUN FROM SOURCE DATABASE

-- Destination Directory and target DB name
declare @ssdirname varchar(1000), @targetdb varchar(255) 
SET @ssdirname = 'X:\MSSnapShots\'
SET @targetdb = DB_NAME() + 'SS'

-- Unique timestamp for filenames
DECLARE @timestamp varchar(50)
SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(varchar(50),GETDATE(),126),':',''),'.',''),'-','')

DECLARE oncmd CURSOR FOR
 select OnCmd = '(NAME=''' + [name] + ''', FILENAME=''' + @ssdirname + [name] + '-' + @timestamp + '.ss'')'
 from sys.database_files
 where [type] = 0

DECLARE @oncmd varchar(500), @sqlcmd varchar(4000)
SET @sqlcmd = ''

OPEN oncmd
FETCH NEXT FROM oncmd INTO @oncmd
WHILE @@FETCH_STATUS = 0
BEGIN
 IF @sqlcmd <> ''
  SET @sqlcmd = @sqlcmd + ', ' + CHAR(10)
 SET @sqlcmd = @sqlcmd + @oncmd

 FETCH NEXT FROM oncmd INTO @oncmd
END
CLOSE oncmd
DEALLOCATE oncmd

SET @sqlcmd = 'CREATE DATABASE ' + @targetdb + ' ON ' + CHAR(10) + @sqlcmd
SET @sqlcmd = @sqlcmd + CHAR(10) + 'AS SNAPSHOT OF ' + DB_NAME()


IF EXISTS (SELECT name FROM sys.databases WHERE name = @targetdb)
 SET @sqlcmd = 'DROP DATABASE ' + @targetdb + ';' + CHAR(10) + @sqlcmd

PRINT @sqlcmd
-- EXEC (@sqlcmd) execute the sql command statement

Viewing the Snapshot

It seems that a snapshot is just a regular database but has been "marked" as a snapshot. Within SSMS, the snapshot can be viewed under Databases → Database Snapshots.

Restoring the Snapshot

Simple Restoration Code

We restore `X_SS` over `X` using this simple code:


USE master;

RESTORE DATABASE X from 
DATABASE_SNAPSHOT = 'X_SS';
GO
Issues when Restoring

When restoring the snapshot, there may be connections to the database we have to kill. StackOverflow - Disconnect active connections has many answers with solutions to this.

Deleting the Snapshot

Deleting a snapshot is just like deleting a regular database:


DROP DATABASE X_SS

No comments: