Backup

MSSQL Snapshot Demo

/ MSSQL Server /

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

MSSQL check database-backup

/ MSSQL Server /

Script to check for databases not backupped in the last two days

SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name not in
(SELECT DISTINCT sys.databases.name from sys.databases 
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'D' AND backup_start_date > (GetDate() -2)))
AND sys.databases.state = 0 AND sys.databases.name ‹› 'tempdb'

 

MSSQL Check TransactionLog Backup

/ MSSQL Server /

Check if the are database without recent (2 days) TransactionLog backups for all databases in FULL Recovery.

SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name NOT IN
(SELECT DISTINCT sys.databases.name from sys.databases 
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2)))
AND sys.databases.recovery_model != 3 and sys.databases.state = 0