Categories
MSSQL Server

MSSQL Snapshot Demo

Snapshot demo script…

/* ##### Maak een test database... ###### */
USE [master]
GO
CREATE DATABASE [TEST1] ON PRIMARY
( NAME = N'TEST1', FILENAME = N'D:MSSQLSERVERTEST1.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TEST1_log', FILENAME = N'D:MSSQLSERVERTEST1_log.ldf' , SIZE = 20480KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

/* ##### Maak een tabel in de database... ##### */
USE [TEST1]
GO
CREATE TABLE [dbo].[Table_1](
[COL1] [int] IDENTITY(1,1) NOT NULL,
[COL2] [nchar](10) NULL
) ON [PRIMARY]
GO

/* ##### Vul de tabel met waarden... ##### */
USE [TEST1]
GO
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Oemupa3l')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Wa4ookei')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Einguix0')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('eeSh8pae')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('The1thoh')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Ba3eih6x')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Ohw8aeYi')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('ZeeC7al0')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('so8Mie3C')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('xu4ohNgo')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('aev3Iepi')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('phe1Eejo')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('aeTiu7ai')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('Vee7Sux7')
INSERT INTO [TEST1].[dbo].[Table_1] ([COL2]) VALUES ('ia3aShu6')
GO

/* ##### Controleer of de tabel is gevuld */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Maak een snapshot van de test database... ###### */
USE [master]
GO
CREATE DATABASE TEST1_Snap ON
( NAME = TEST1, FILENAME =
'D:MSSQLSERVERTEST1_SNAP.mdf' )
AS SNAPSHOT OF [TEST1];

/* ##### Selecteer waarden uit de snapshot... ###### */
USE [TEST1_Snap]
GO
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
GO

/* ### Verwijder waarden uit de tabel... ##### */
USE [TEST1]
GO
DELETE FROM [TEST1].[dbo].[Table_1]
WHERE COL1 = 10
GO

/* ##### Controleer of de waardes zijn verdwenen... */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Selecteer waarden uit de snapshot... ###### */
USE [TEST1_Snap]
GO
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
GO

/* ##### Als je zeker weet dat er geen gerelateerde gegevens zijn verdwenen ##### */
/* Set Identity insert ON */
USE [TEST1]
GO
SET IDENTITY_INSERT [TEST1].[dbo].[Table_1] ON
GO

/* Zet verwijderde record terug */
USE [TEST1]
GO
INSERT INTO [TEST1].[dbo].[Table_1] (COL1, COL2)
SELECT * FROM [TEST1_Snap].[dbo].[Table_1]
WHERE COL1 = 10

/* ##### Controleer of de tabel is gevuld ##### */
USE [TEST1]
GO
SELECT * FROM [TEST1].[dbo].[Table_1]
GO

/* ##### Restore volledige database van snapshot... ##### */
USE [master]
GO
RESTORE DATABASE [TEST1] FROM DATABASE_SNAPSHOT = 'TEST1_Snap'
GO