Uncategorized

Create and restore database from database snapshot

9. 3. 2018
USE [master]
GO

CREATE DATABASE [MY_DB_01] ON
( NAME = N'MY_DB', FILENAME = N'E:\DATA\MY_DB.ss' )
AS SNAPSHOT OF [MY_DB];
GO

-- REVERT TO SNAPSHOT

USE [master]
GO

-- Close existing connections
DECLARE @Sql VARCHAR(1000)
SET @Sql = ''    
SELECT  @Sql = @Sql + 'kill ' + CONVERT(NVARCHAR(3), [spid]) + '; '
FROM    [master].[dbo].[sysprocesses]
WHERE   DB_NAME([dbid]) = 'MY_DB' 
EXECUTE(@Sql)
GO

-- Restore from snapshot
RESTORE DATABASE MY_DB
    FROM DATABASE_SNAPSHOT = 'MY_DB_01';
GO

 

141
Share: