Uncategorized

Create and restore database from database snapshot

Transact-SQL 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 1234567891011121314151617181920212223242526 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 connectionsDECLARE @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 snapshotRESTORE DATABASE MY_DB    FROM DATABASE_SNAPSHOT = 'MY_DB_01';GO