USE [msdb]
GO
/****** Object:  StoredProcedure [custom].[sp_snapshot_cerate]    Script Date: 16.02.2015 16:12:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		XXXXX
-- Create date: 2015.02.16
-- Description:	Создание моментального снимка базы
-- Sample: msdb.custom.sp_snapshot_cerate 'MyDatabase'  
-- =============================================
ALTER PROCEDURE [custom].[sp_snapshot_cerate] 
	@db_name sysname 
AS
BEGIN	
	SET NOCOUNT ON;
    
declare @file_logical_name sysname, @file_path sysname, @sql nvarchar(4000),@dat varchar(23), @num int, @snaoshot_name sysname;

set @dat = convert(varchar(8),getdate(),112)+'_'+replace(convert(varchar(5),getdate(),108),':','')
set @snaoshot_name = @db_name+'_SNAPSHOT_'+@dat;
set @sql= 'CREATE DATABASE ['+@snaoshot_name+'] ON' + char(13)
set @num = 1;

declare cur cursor for 
select mf.name, mf.physical_name from sys.databases db join sys.master_files mf on db.database_id=mf.database_id  where db.source_database_id is NULL and mf.type_desc in ('ROWS') and db.name  = @db_name order by mf.file_id 
open cur
fetch next from cur into @file_logical_name, @file_path
while @@FETCH_STATUS =0
begin    
    set @file_path=substring(@file_path,1,len(@file_path)-4)
    set @sql = @sql + CASE WHEN @num = 1 then ' ' else ',' end
	set @sql = @sql + '(NAME = '+@file_logical_name+' ,  FILENAME = '''+@file_path+'_'+@dat+'_Dat'+cast(@num as varchar(2))+'.ss'')' + char(13) 
	set @num = @num+1;
	fetch next from cur into @file_logical_name, @file_path
end
close cur
deallocate cur
set @sql = @sql + 'AS SNAPSHOT OF [' + @db_name + '];'

IF @num>1
BEGIN
  exec sp_executesql @sql;
  print 'SNAPSHOT ' + @snaoshot_name + ' создан'
END
ELSE
BEGIN
  select 'SNAPSHOT не создан: неправильное имя базы или другая причина';
END

END

USE [msdb]
GO
/****** Object:  StoredProcedure [custom].[sp_snapshot_cerate]    Script Date: 16.02.2015 16:12:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		XXXXX
-- Create date: 2015.02.18
-- Description:	восстанавливает базу из моментального снимка
-- Sample1: msdb.custom.sp_snapshot_restore_from 'TEST_SNAPSHOT_20150218_1413',0
-- Sample2: msdb.custom.sp_snapshot_restore_from 'TEST_SNAPSHOT_20150218_1413',1
-- =============================================
CREATE PROCEDURE [custom].[sp_snapshot_restore_from]	
	 @snapshot_name sysname --имя моментального снимка. имя восстанавливаемой базы определится автоматически, по имени снимка.
	,@kill_sessions bit	    --1 чтоб принудительно разорвать все сеансы с базой
AS
BEGIN	
	SET NOCOUNT ON;

	declare @source_database_name sysname, @source_database_id int   
	select @source_database_name=db_name(sd.source_database_id), @source_database_id=source_database_id from sys.databases sd where sd.source_database_id is not NULL and name = @snapshot_name
	
	if @source_database_name is NULL
	BEGIN
	  print 'проверьте параметр имя снимка: исходная база не найдена'
	  return 1
	END

	if exists(select source_database_id from sys.databases where source_database_id in (select source_database_id from sys.databases sd where sd.source_database_id is not NULL and name = @snapshot_name) group by source_database_id having count(*) > 1)
	BEGIN
	  print 'перед восстановлением Вам необходимо удалить все другие снимки'
	  return 2
	END

	if exists(select * from sys.sysprocesses where dbid=@source_database_id) and isnull(@kill_sessions,0)=0
	BEGIN
	  print 'восстановление не начато: к подлежащей восстановлению базе есть открытые сессии. чтоб закрыть сессии автоматически, установите 1 в параметре @kill_sessions'
	  return 3
	END

	declare @spid int, @sql nvarchar(500);
	declare cur cursor for select spid from sys.sysprocesses where dbid=@source_database_id
	open cur
	fetch next from cur into @spid
	while @@FETCH_STATUS=0
	BEGIN
	  set @sql = 'kill ' + cast(@spid as varchar(10));
	  exec sp_executesql @sql;
	  fetch next from cur into @spid
	END
	close cur
	deallocate cur

	BEGIN TRY	
		set @sql = 
		'use master; ' + char(13) +
		'RESTORE DATABASE '+ @source_database_name + ' from DATABASE_SNAPSHOT = '''+@snapshot_name+''';'
		exec sp_executesql @sql;
		print 'база [' + @source_database_name + '] восстановлена'
	END TRY
	BEGIN CATCH
	    print 'база [' + @source_database_name + '] ошибка при восстановлении : ' + ERROR_MESSAGE()
	END CATCH
		
END

USE [msdb]
GO
/****** Object:  StoredProcedure [custom].[sp_snapshot_cerate]    Script Date: 24.02.2015 14:36:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		XXXXXXX
-- Create date: 2015.02.16
-- Description:	Создание моментального снимка базы
-- Sampes: 
--	msdb.custom.sp_snapshot_cerate 'myDatabase'						--имя снимка: авто; расположение: рядом с файлами базы 
--	msdb.custom.sp_snapshot_cerate 'myDatabase','mySnapshot'				--имя снимка: из параметра; расположение: рядом с файлами базы   
--	msdb.custom.sp_snapshot_cerate 'myDatabase','mySnapshot',	'C:\Temp\'		--имя снимка: из параметра; расположение: из параметра   
--	msdb.custom.sp_snapshot_cerate 'myDatabase', NULL, 		'C:\Temp\'		--имя снимка: авто; расположение: из параметра
-- =============================================
CREATE PROCEDURE [custom].[sp_snapshot_cerate] 
	@db_name sysname, @snapshot_name sysname = NULL, @snapshot_folder sysname=null
AS
BEGIN	
	SET NOCOUNT ON;
    
declare @logical_name sysname, @folder sysname, @filename_without_extention sysname, @sql nvarchar(4000),@dat varchar(23), @num int, @msg nvarchar(4000);

set @dat = convert(varchar(8),getdate(),112)+'_'+replace(convert(varchar(5),getdate(),108),':','')
set @snapshot_name = isnull(@snapshot_name,@db_name+'_SNAPSHOT_'+@dat);
set @sql= 'CREATE DATABASE ['+@snapshot_name+'] ON' + char(13)
set @num = 1;

if charindex('\',reverse(@snapshot_folder))<>1
  set @snapshot_folder=@snapshot_folder+'\'
     
declare cur cursor for 
  select 
   logical_name               = mf.name
  ,folder                     = isnull(@snapshot_folder, reverse(substring(reverse(mf.physical_name),charindex('\',reverse(mf.physical_name)),1000))) 
  ,filename_without_extention = reverse(substring(reverse(mf.physical_name),  charindex('.',reverse(mf.physical_name))+1,  charindex('\',reverse(mf.physical_name))-charindex('.',reverse(mf.physical_name))-1))  
  from sys.databases db join sys.master_files mf on db.database_id=mf.database_id  
  where db.source_database_id is NULL and mf.type_desc in ('ROWS') and db.name  = @db_name order by mf.file_id 

open cur
fetch next from cur into @logical_name, @folder, @filename_without_extention
while @@FETCH_STATUS =0
begin
	    
    set @sql = @sql + CASE WHEN @num = 1 then ' ' else ',' end
	set @sql = @sql + '(NAME = '+@logical_name+' ,  FILENAME = '''+@folder+@filename_without_extention+'_'+@dat+'_Dat'+cast(@num as varchar(2))+'.ss'')' + char(13) 
	set @num = @num+1;
	fetch next from cur into @logical_name, @folder, @filename_without_extention
end
close cur
deallocate cur
set @sql = @sql + 'AS SNAPSHOT OF [' + @db_name + '];'

IF @num>1
BEGIN
	BEGIN TRY	  
	  exec sp_executesql @sql;  
	  set @msg = 'снимок ' + @snapshot_name + ' создан';
	  RAISERROR (@msg , 0, 0);  	  	  
	END TRY
	BEGIN CATCH
	  set @msg = 'снимок не создан:' +  ERROR_MESSAGE();
	  RAISERROR (@msg , 16, 1);  
	END CATCH
END
ELSE
BEGIN
	set @msg = 'снимок не создан: неправильное имя базы или другая причина' ;
	RAISERROR (@msg , 16, 1);  
END
END

USE [msdb]
GO
/****** Object:  StoredProcedure [custom].[sp_snapshot_restore_from]    Script Date: 24.02.2015 14:46:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		XXXXXXX
-- Create date: 2015.02.18
-- Description:	восстанавливает базу из моментального снимка
-- Beispiele: 
--   msdb.custom.sp_snapshot_restore_from 'TEST_SNAPSHOT_20150218_1413', 0 --восстановить базу, если к ней нет открытых сессий
--   msdb.custom.sp_snapshot_restore_from 'TEST_SNAPSHOT_20150218_1413', 1 --восстановить базу, предварительно разорвав открытые сессии
-- =============================================
CREATE PROCEDURE [custom].[sp_snapshot_restore_from]	
	@snapshot_name sysname, @kill_sessions bit
AS
BEGIN	
	SET NOCOUNT ON;

	declare @source_database_name sysname, @source_database_id int, @sql nvarchar(4000), @msg nvarchar(4000);   
	select @source_database_name=db_name(sd.source_database_id), @source_database_id=source_database_id from sys.databases sd where sd.source_database_id is not NULL and name = @snapshot_name
	
	if @source_database_name is NULL
	BEGIN
	  RAISERROR('проверьте параметр имя снимка: исходная база не найдена',16,1);
	  return 1
	END

	if exists(select source_database_id from sys.databases where source_database_id in (select source_database_id from sys.databases sd where sd.source_database_id is not NULL and name = @snapshot_name) group by source_database_id having count(*) > 1)
	BEGIN
	  RAISERROR('перед восстановлением Вам необходимо удалить все другие снимки',16,1);
	  return 2
	END	

	set @sql='';
	select @sql = @sql + 'kill ' + cast(spid as varchar(10)) + ';' from sys.sysprocesses where dbid=@source_database_id
	
	if @sql<>'' and isnull(@kill_sessions,0)=0
	BEGIN
	  RAISERROR('восстановление не начато: к подлежащей восстановлению базе есть открытые сессии. чтоб закрыть сессии автоматически, установите 1 в параметре @kill_sessions',16,1);
	  return 3
	END

	BEGIN TRY	
	    exec sp_executesql @sql;

		set @sql = 
		'use master; ' + char(13) +
		'RESTORE DATABASE '+ @source_database_name + ' from DATABASE_SNAPSHOT = '''+@snapshot_name+''';'
		exec sp_executesql @sql;

		set @msg = 'база  [' + @source_database_name + '] восстановлена';
		RAISERROR(@msg,0,0);
	END TRY
	BEGIN CATCH
	    set @msg = 'база  [' + @source_database_name + '] не восстановлена. ошибка: ' + ERROR_MESSAGE();
	    RAISERROR(@msg,16,1); 
	END CATCH
	

	
END