SQL 2008 MCM Studies - Restore Internals

/*
14 - Restore Internal.sql
Remark: Learned from sqlskills.com
*/

IF DATABASEPROPERTY('MCM2008', 'Version') > 0
DROP DATABASE MCM2008;

-- Create the database
CREATE DATABASE MCM2008;
GO

-- Create a dummy device
EXEC SP_ADDUMPDEVICE 'disk',
'MyBackupDevice',
'C:\SQLskills\MyBigbackup.bak';
GO

-- Create a table
CREATE TABLE MCM2008..TestTable (
c1 INT IDENTITY,
c2 VARCHAR (100));
GO

INSERT INTO MCM2008..TestTable
VALUES ('INITIAL DATA: transaction 1');
GO

-- And take a full backup
BACKUP DATABASE MCM2008 TO
MyBackupDevice
WITH FORMAT,
DESCRIPTION='Initial full datatabase Backup';
GO

-- Now add some more data and a backup
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 2');
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 3');
GO

BACKUP LOG MCM2008 TO MyBackupDevice
WITH DESCRIPTION='First Log Backup';
GO

-- Add more data
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 4');
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 5');
GO

-- Now check the time and save it
SELECT GETDATE()
GO
--2011-01-31 20:28:44.700

-- And add some more data
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 6');
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 7');
GO

-- And take another backup
BACKUP LOG MCM2008 TO MyBackupDevice
WITH DESCRIPTION='Second log backup';
GO

-- Simulate disater
DROP DATABASE MCM2008;
GO

-- So what do we have
RESTORE HEADERONLY FROM MyBackupDevice;
GO
/*
BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize
----------------- ----------------------------------------- ---------- ----------------- ---------- -------- ---------- ------------------------- ------------------ -------------------- --------------- ----------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- ----------------------- --------- -------- --------------- ---------------------- ------------------ ---------------- -------------------- -------------------- -------------------- -------------------- ----------- ------------------------------------ ------------------------------------ --------------------------------------- ------------------------------------ ----------------- ---------- ---------- ------------ ------------------ --------- -------------- --------------------- -------------- ---------- ------------------------------------ ------------------ ----------------------- --------------------------------------- ------------------------------------ ----------------------------- ------------------------------------ --------------------
NULL Initial full datatabase Backup 1 NULL 0 1 102 WINPOC\Administrator WINPOC\SQL08 MCM2008 655 2011-01-31 20:13:56.000 1387520 21000000010100151 21000000016200001 21000000010100151 21000000005400064 2011-01-31 20:22:54.000 2011-01-31 20:22:54.000 52 0 1033 196609 100 4608 10 0 4000 WINPOC 512 1E9EEAE3-2408-4F86-802C-8D91382098E9 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 SQL_Latin1_General_CP1_CI_AS 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 0 0 0 0 0 0 0 0 0 0 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 NULL FULL NULL NULL Database D678602C-5203-46A4-9264-A26A4DA8BA9F 1387520
NULL First Log Backup 2 NULL 0 2 102 WINPOC\Administrator WINPOC\SQL08 MCM2008 655 2011-01-31 20:13:56.000 73728 21000000009500001 21000000019900001 21000000010100151 21000000010100151 2011-01-31 20:26:51.000 2011-01-31 20:26:51.000 52 0 1033 196609 100 4608 10 0 4000 WINPOC 512 1E9EEAE3-2408-4F86-802C-8D91382098E9 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 SQL_Latin1_General_CP1_CI_AS 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 0 0 0 0 0 0 0 0 0 0 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 NULL FULL NULL NULL Transaction Log 113BAE7D-E6F5-4A9E-BD3A-39B981456835 73728
NULL Second log backup 2 NULL 0 3 102 WINPOC\Administrator WINPOC\SQL08 MCM2008 655 2011-01-31 20:13:56.000 73728 21000000019900001 21000000020300001 21000000010100151 21000000010100151 2011-01-31 20:30:41.000 2011-01-31 20:30:41.000 52 0 1033 196609 100 4608 10 0 4000 WINPOC 512 1E9EEAE3-2408-4F86-802C-8D91382098E9 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 SQL_Latin1_General_CP1_CI_AS 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 0 0 0 0 0 0 0 0 0 0 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 NULL FULL NULL NULL Transaction Log F8137C8F-6A3E-4B78-A0FE-5DA0B74893D4 73728

(3 row(s) affected)

*/

-- Restore the full backup
RESTORE DATABASE MCM2008
FROM MyBackupDevice
WITH FILE=1, STATS, REPLACE,NORECOVERY;
GO

-- And the log backups
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=2,NORECOVERY;

RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=3,NORECOVERY;
GO

-- And then finalize recovery
RESTORE DATABASE MCM2008 WITH RECOVERY;
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1
2 Transaction 2
3 Transaction 3
4 Transaction 4
5 Transaction 5
6 Transaction 6
7 Transaction 7

(7 row(s) affected)
*/
-- What if we want to examine things
-- between restores?
RESTORE DATABASE MCM2008
FROM MyBackupDevice
WITH FILE=1, REPLACE,
STANDBY='C:\SQLskills\standbyfile.dat';
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1

(1 row(s) affected)
*/
-- Can we update anything?
INSERT INTO MCM2008..TestTable
VALUES('Transaction 6');
GO
/*
Msg 3906, Level 16, State 1, Line 1
Failed to update database "MCM2008" because the database is read-only.
*/


-- Let's do the next log file
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=2,
STANDBY='C:\SQLskills\standbyfile.dat';
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1
2 Transaction 2
3 Transaction 3

(3 row(s) affected)
*/

-- Now restore the rest, and bring
-- the database online
/*
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=3, STANDBY='C:\SQLskills\standbyfile.dat';
GO
*/
RESTORE LOG MCM2008
FROM MyBackupDevice
WITh FILE=3, NORECOVERY;
GO

RESTORE DATABASE MCM2008 WITH RECOVERY;
GO

-- what if we want to stop at a specific point?
RESTORE DATABASE MCM2008
FROM MyBackupDevice
WITH FILE=1, NORECOVERY,REPLACE,
STOPAT='2011-01-31 20:28:44.700';
GO
/*Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
*/
/*
EXEC sp_who2;
GO

KILL 60;
GO

ALTER DATABASE MCM2008 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
*/
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=2,
STOPAT='2011-01-31 20:28:44.700',
STANDBY='C:\SQLskills\standbyfile.dat';

/*
Processed 0 pages for database 'MCM2008', file 'MCM2008' on file 2.
Processed 7 pages for database 'MCM2008', file 'MCM2008_log' on file 2.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 7 pages in 0.008 seconds (6.347 MB/sec).
*/

RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=3,
STOPAT='2011-01-31 20:28:44.700',
STANDBY='C:\SQLskills\standbyfile.dat';
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1
2 Transaction 2
3 Transaction 3
4 Transaction 4
5 Transaction 5

(5 row(s) affected)
*/

-- Finalize recovery, with it same as below:
-- RESTORE LOG MCM2008
-- FROM MyBackUpDevice
-- WITH FILE=3, RECOVERY,
-- STOPAT='2011-01-31 20:28:44.700';

RESTORE DATABASE MCM2008 WITH RECOVERY;
GO
SELECT * FROM MCM2008..TestTable;
GO


-- Take another backup so we don't need
-- to go through all that again
BACKUP DATABASE MCM2008 TO
MyBackupDevice
WITH DESCRIPTION='Full backup after recovery';
GO