Creating a Database Snapshot

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick "create database xx" syntax I often use, with a snapshot I need to be more specific.

The big item that I must specify is the location of the snapshot file. This is the file that gets written to with the copy-on-write process that ensures the original state of the pages when the snapshot is created are still available.

You do need to give the database a logical name as well, which can be anything, but the reference below has suggestions. I’d say that this is bad:

CREATE DATABASE sandbox_snapshot
ON (NAME = MySnap
, FILENAME = ‘h:\SQL Server\MySnap.ss’
)
AS SNAPSHOT OF Sandbox

But this is better

CREATE DATABASE sandbox_snapshot_20150122_1345
ON (NAME = SandBox_Snap_20150122
, FILENAME = ‘h:\SQL Server\MySnap.ss’
)
AS SNAPSHOT OF Sandbox

Because the snapshot is based on an existing database, at a particular point in time, it’s useful to specify the time when the snapshot was created, if possible. While you can get this from metadata, if you have people that look to multiple snapshots for information, it can be handy to know when each is from.

However if you are providing this on a schedule, like daily, for people to report from, you might need to have the same name every day. Think about this, as you cannot rename a snapshot once it’s created.

SQLNewBlogger

When I realized I had to lookup the syntax, I took a few notes and captured code, which meant I combined this writing (< 10 minutes) with other work I was doing.

You should do the same. When you tackle something new, take screenshots, save code, and drop it in a OneNote/EverNote/etc notebook for your weekly blog writing.

Reference

The references I used:

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

One Response to Creating a Database Snapshot

  1. Yankov says:

    I often have to use Snapshot, which is why you wrote a procedure for creating respectively a restore.With their help, my job very easy to create a snapshot just execute:

    exec [sp_db_snapshot_create] ‘DataBaseName’

    ,and to restore the database snapshot:

    exec [sp_db_snapshot_restore] ‘DataBaseSnapshot’

    and the code of the procedures :

    USE [master]
    GO

    create proc [dbo].[sp_db_snapshot_create]( @DbName nvarchar(255), @LogicalFileName nvarchar(256) = NULL, @ForAllFiles int = 0 )
    as
    begin

    declare @Ret int, @SnapShotCount int, @DBaseId int, @@SSId nvarchar(16)
    select @Ret = -1, @DBaseId = DB_ID(@DbName), @SnapShotCount = 1

    if @DbName is null or @DBaseId is null
    begin
    print (‘There is not database with name “‘+@DbName+'” on server ‘+@@SERVERNAME+’!’ )
    return -1
    end

    if @LogicalFileName is not null and Not exists
    ( select * from sys.master_files where database_id = @DBaseId and name = @LogicalFileName )
    begin
    print (‘There is not logical file with name “‘+@LogicalFileName+'” on database ‘+@DbName+’!’ )
    return -2
    end

    select @SnapShotCount = count(*) + 1
    from sys.databases where source_database_id = @DBaseId

    set @@SSId = N’SS@’+convert(char(8),GetDate(),112 )+’V’ + REPLACE( STR(@SnapShotCount,3,0),’ ‘, ”)

    declare @Sql nvarchar(4000), @CrRt nvarchar(8), @FilList nvarchar(4000)
    select @Sql = ”, @CrRt = nchar(13) + nchar(10), @FilList = ”

    declare @LogicalFile nvarchar(255), @PhysicalFile nvarchar(512), @FileId int, @FIdStr nvarchar(16)
    select @LogicalFile = ”, @PhysicalFile = ”, @FileId = -1

    declare curDBFile cursor
    local forward_only read_only for
    select [mf].[file_id], [mf].[name], [mf].[physical_name]
    from sys.master_files [mf]
    where database_id = DB_ID(@DbName)
    and [mf].type = 0 /* ROWS */
    and (case when @LogicalFileName is null then ” else [mf].[name] end) = ISNULL( @LogicalFileName, ” )

    open curDBFile

    fetch next from curDBFile
    into @FileId, @LogicalFile, @PhysicalFile

    while @@FETCH_STATUS = 0
    begin
    set @FIdStr = REPLACE( STR( @FileId, 4, 0 ), ‘ ‘,” )
    set @Sql = N'( NAME = ‘+@LogicalFile+’, FILENAME = ”’+@PhysicalFile+’_SS’+@FIdStr+’.’+@@SSId+”’ ) ‘ + @CrRt

    set @FilList = @FilList
    + case when @FilList = ” then N” else N’,’ end
    + @Sql

    fetch next from curDBFile
    into @FileId, @LogicalFile, @PhysicalFile

    if @ForAllFiles = 0
    break
    end
    close curDBFile
    deallocate curDBFile

    set @Sql = N’CREATE DATABASE [‘+@DbName+’_’+@@SSId+’] ON ‘ + @CrRt
    + @FilList
    + N’AS SNAPSHOT OF ‘+@DbName+’;’

    exec @Ret = sp_executeSql @Sql
    if @@ERROR 0 or @Ret 0
    begin
    print (N’Error execute Sql:”‘+@Sql+'”!’)
    return 1
    end

    return @Ret
    end
    go

    create proc [dbo].[sp_db_snapshot_restore]( @DBSnapShot nvarchar(256), @DropConnections int = 0 )
    as
    begin
    declare @Ret int, @Sql nvarchar(4000), @SorceDB int

    if DB_ID(@DBSnapShot) is null
    begin
    print (‘There is not snapshot with name “‘+@DBSnapShot+'” on server ‘+@@SERVERNAME+’!’ )
    return -1
    end

    select @SorceDB = source_database_id from sys.databases
    where database_id = DB_ID(@DBSnapShot)

    if @SorceDB is null
    begin
    print (‘[‘+@DBSnapShot+’] is not database snapshot!’ )
    return -1
    end

    if @DropConnections 0
    begin

    declare @SID int, @loginame nvarchar(256)

    declare curSPIDs cursor
    local forward_only read_only for
    select spid, loginame
    from sys.sysprocesses with (nolock)
    where spid @@SPID
    and hostname ”
    and dbid = @SorceDB

    open curSPIDs
    fetch next from curSPIDs into @SID, @loginame

    while @@FETCH_STATUS = 0
    begin
    set @Sql = ‘KILL ‘ + STR( @SID )
    exec @Ret = sp_executeSql @Sql

    if @@ERROR 0 or @Ret 0
    print (N’Error execute Sql:”‘+@Sql+'”!’)

    fetch next from curSPIDs into @SID, @loginame
    end

    close curSPIDs
    deallocate curSPIDs

    end

    declare @SourceDBase nvarchar(256), @CrLf nvarchar(8)
    select @SourceDBase = DB_NAME(@SorceDB), @CrLf = NCHAR(13) + NCHAR(10)

    set @Sql = N’RESTORE DATABASE [‘+@SourceDBase+’]’ + @CrLf
    + N’FROM DATABASE_SNAPSHOT = ”’+@DBSnapShot+”’;’

    exec @Ret = sp_executeSql @Sql
    if @@ERROR 0 or @Ret 0
    begin
    print (N’Error execute Sql:”‘+@Sql+'”!’)
    set @Ret = 1
    end

    return @Ret
    end

Comments are closed.