Restore With Standby

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Database Administration - Backup & Recovery

The WITH STANDBY option for the SQL Server Restore command lets you restore your full backup, then run a few queries before you keep restoring transaction logs.

Demo Video

In this five-minute video, Brent shows how to use the Standby option for restores:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090205-RestoreWithStandby.flv

Demo Script

Step through this script executing each group of statements one at a time to see the effects. Make sure to correct the file paths for the databases and the backup files to reflect your own environment. 

/* Create our test database.  You may need to change the file paths for your environment. */
CREATE DATABASE [DisasterDatabase] ON  PRIMARY 
( NAME = N'DisasterDatabase', FILENAME = N'C:\MSSQL\DATA\DisasterDatabase.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DisasterDatabase_log', FILENAME = N'C:\MSSQL\LOGS\DisasterDatabase_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DisasterDatabase] SET RECOVERY FULL 
GO
USE [DisasterDatabase]
GO



/* Create some dummy data */
CREATE TABLE dbo.Customers (CustomerID INT, CustomerName VARCHAR(50))
GO
INSERT INTO dbo.Customers (CustomerID, CustomerName)
  VALUES (1, 'Bill Gates')
INSERT INTO dbo.Customers (CustomerID, CustomerName)
  VALUES (2, 'Steve Ballmer')
GO



/* Do a full backup of the database */
BACKUP DATABASE [DisasterDatabase] TO  DISK = N'C:\MSSQL\BACKUP\DisasterDatabase.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'DisasterDatabase-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



/* Add some more dummy data */
INSERT INTO dbo.Customers (CustomerID, CustomerName)
  VALUES (3, 'Donald Farmer')



/* Do a transaction log backup */
BACKUP LOG [DisasterDatabase] TO  DISK = N'C:\MSSQL\BACKUP\DisasterDatabase.trn' 
WITH NOFORMAT, NOINIT,  
NAME = N'DisasterDatabase-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



/* Pretend we're a developer */
DROP TABLE dbo.Customers
GO



/* DISASTER!  We need to go back in time! */
USE master
GO
RESTORE DATABASE [DisasterDatabase] 
  FROM  DISK = N'C:\MSSQL\BACKUP\DisasterDatabase.bak' 
  WITH  FILE = 2,
  REPLACE,
  STANDBY = N'C:\MSSQL\BACKUP\ROLLBACK_UNDO_DisasterDatabase.BAK'
GO



/* Test to see if we can read records */
SELECT * FROM DisasterDatabase.dbo.Customers
/* Notice that we only got 2 customers, because we haven't restored the t-log yet. */



RESTORE LOG [DisasterDatabase] 
  FROM  DISK = N'C:\MSSQL\BACKUP\DisasterDatabase.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO



/* Test to see if we can read records */
SELECT * FROM DisasterDatabase.dbo.Customers


More Articles on SQL Server Backups

Retrieved from "http://sqlserverpedia.com/wiki/Restore_With_Standby"