SQL DB - Point-In-Time Restore Exercise

[Mic Drop]

Below is a script that should be ran step by step and not all at once.  It’ll help you understand backup methodology and the importance of knowing how to recover to a point-in-time.  This is something a DBA will seldom have to do, but when the time comes, they’ll have to deliver as part of their core job responsibilities.  Nothing is more fundamental to a DBA than the ability to recover to a PIT.  

/*
Prerequisites: You should be a local administrator in Windows and a SQL Server sysadmin
Purpose: The purpose of this exercise to simulate a production database's catastrophic failure
and the steps a database administrator will need to take to bring production back on line. To simulate
work pressure of a real production down scenario, understand these steps and syntax, and time yourself on how
quickly you are able to recover the business transactions, and validate that they were restored.
*/

--Create test database

USE MASTER
CREATE DATABASE [MyTestDB]
GO

--Create test table

CREATE TABLE [MyTestDB]..[MyTable]
(C1 INT)
GO

--Create full backup of test database

BACKUP DATABASE [MyTestDB] TO DISK = 'C:\TEMP\MyTestDB.bak'
GO

--Create several new records to simulate business transactions

USE MyTestDB;
INSERT INTO MyTable VALUES (1)
GO 5

--Backing up transaction log to harden the business transactions to a transaction log backup file on disk

BACKUP LOG [MyTestDB] TO DISK = 'C:\TEMP\MyTestDB_Log.trn';

--Dropping database to simulate catastrophic database failure

USE MASTER
ALTER DATABASE [MyTestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [MyTestDB];
GO

--Restoring full database backup WITH NORECOVERY to simulate recovery process

USE MASTER
RESTORE DATABASE [MyTestDB] FROM DISK = 'C:\TEMP\MyTestDB.bak' WITH NORECOVERY

--Restoring transaction log backup WITH RECOVERY to simulate restoring a transaction log backup to a point-in-time

RESTORE LOG [MyTestDB] FROM DISK = 'C:\TEMP\MyTestDB_Log.trn' WITH RECOVERY
GO

--Validation that the 5 business transactions were restored

SELECT * FROM [MyTestDB]..[MyTable]