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:
-
Create a Database Snapshot – https://msdn.microsoft.com/en-us/library/ms175876.aspx
Voice of the DBA 
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
LikeLike