Jen McCown’s (blog | twitter) latest assignment in the Applied SQL series deals with creating a simple database to be used to demonstrate backup and restore procedures.  The assignment takes us through creating a database with a single table, scripting full, transactional and differential backups and finally several restores of the database.

On to the assignment…

Setup:

• Create a database named testing.
• Set it to FULL recovery mode.
• Create a simple table (T1) in it with at least one varchar(100) column.
• Insert a few rows into your table.
• Add a “drop database; GO ” at the beginning of your script, so you’ll have a clean start each time.

USE master;
GO
/*
** Drop the Testing Database if it exists
*/
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Testing')
BEGIN
      DROP DATABASE Testing;
END
GO

/*
** Create the Testing Database
*/

CREATE DATABASE Testing
ON
(
      NAME = testing_dat,
    FILENAME = 'C:\MSSQL\testing.mdf'
    ,SIZE = 10MB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 5MB
)
LOG ON
( NAME = testing_log,
    FILENAME = 'C:\MSSQL\testing.ldf'
    ,SIZE = 5MB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 5MB
) ;
GO

ALTER DATABASE Testing
      SET RECOVERY FULL;
GO

USE Testing
GO


CREATE TABLE T1
(
      KeyVal   INT IDENTITY(1,1) NOT NULL
      ,DataVal VARCHAR(100) NOT NULL
);
GO

INSERT INTO T1
VALUES ('First Row - After CREATE TABLE')
,('Row 002')
,('Row 003')
,('Row 004')
,('Row 005')
,('Row 006');
GO

Level 1 Assignments: Backups – after the Setup section in your script, add the following:

[1-1] Full backup

• Backup the database to disk, filename testing.bak
• Insert a few more rows into your table.

-- FULL BACKUP
BACKUP DATABASE Testing
      TO DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH INIT, FORMAT;
GO

INSERT INTO T1
VALUES ('Row Inserted After FULL Backup')
,('Row 008')
,('Row 009')
,('Row 010')
,('Row 011')
,('Row 012');
GO

[1-2] TLOG backup

• Backup the LOG to disk, filename testingLOG1.bak
• Insert a few more rows into your table.

-- TLOG
BACKUP LOG Testing
      TO DISK = 'C:\MSSQL\BACKUPS\TestingLOG1.bak'
      WITH INIT, FORMAT;
GO


INSERT INTO T1
VALUES ('Row Inserted After First TLOG Backup')
,('Row 014')
,('Row 015')
,('Row 016')
,('Row 017')
,('Row 018');
GO

[1-3] DIFF backup, one more TLOG backup

• Take a DIFFERENTIAL backup of the database, filename testingDIFF2.bak
• Insert a few more rows into your table.
• Backup the LOG to disk, filename testingLOG3.bak
• Insert a few more rows into your table

-- DIFFERENTIAL 
BACKUP DATABASE Testing
      TO DISK = 'C:\MSSQL\BACKUPS\TestingDIFF2.bak'
      WITH DIFFERENTIAL, INIT, FORMAT;
GO

INSERT INTO T1
VALUES ('Row Inserted After Differential Backup')
,('Row 020')
,('Row 021')
,('Row 022')
,('Row 023')
,('Row 024');
GO

-- TLOG
BACKUP LOG Testing
      TO DISK = 'C:\MSSQL\BACKUPS\TestingLOG3.bak'
      WITH INIT, FORMAT;
GO

INSERT INTO T1
VALUES ('Row Inserted After Second TLOG Backup')
,('Row 026')
,('Row 027')
,('Row 028')
,('Row 029')
,('Row 030');
GO

Setup2: The next step was to go back and modify the BACKUP statements to include INIT and FORMAT.   INIT and FORMAT fall under Media Set Options of the backup command.

INIT specifies that the backup set should be overwritten if it exists.  The default NOINIT, specifies that the backup set would be appended to.

FORMAT, specifies that a new media set be created by writing a new media header on all volumes used in the backup operation.  Whereas, NOFORMAT, the default behavior,  specifies that the existing media headers are kept

Level 2 Assignments: Restores

Now that we’ve done a variety of backups in the level 1 assignments we move on to level 2 where we drop the database and apply different restores to demonstrate restoring the various backups done above.

Prior to dropping the database if we should view the current data.

SELECT * FROM T1;
GO

Table T1 contains 30 rows.

[2-1] Restore FULL

For this exercise, we’re to restore the FULL backup taken in exercise [1-1] and then select the data from the restored table.

-- FULL Restore
DROP DATABASE Testing;
GO

RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH RECOVERY;
GO

USE Testing;
GO
SELECT * FROM T1;
GO

Selecting all rows from the table retrieves 6 rows, rather than 30.  Which is correct, these were the only rows inserted into the table T1 prior to the FULL backup taken in exercise [1-1].  The 24 rows, not included in the restore were those rows inserted after the FULL backup

[2-2] Restore FULL, DIFF

• Restore the FULL and DIFFERENTIAL backups of the database (from steps 1-1 and 1-3 above).

-- FULL Restore followed by DIFFERENTIAL
USE master;
GO
DROP DATABASE Testing;
GO

RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH NORECOVERY;
     
GO
RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\TestingDIFF2.bak'
      WITH RECOVERY;
GO

USE Testing;
GO

SELECT * FROM T1;
GO

The select against T1 returns the following result set consisting of the first 18 rows inserted into the table, which were all the rows inserted just prior to the differential backup.

One statement/question from the assignment is:

• Get the tlog1/diff relationship straight in your head…do you know why we skipped right over the tlog1 backup?

Which raises a good point.  SQL Server supports 3 types of backups:

• FULL Backup: Contains all of the data in the database being backed up.  And acts as the baseline in the event of needing to restore the database.  Meaning, that the FULL backup must be restored first and then any subsequent DIFFERENTIAL and/or TRANSACTION LOG backups would be applied.

• DIFFERENTIAL Backup: A DIFFERENTIAL backup contains the data which has changed since the last differential base.  The differential base, is the latest FULL backup of the database. 

  The size of the DIFFERENTIAL Backups increase over time as more data changes within the database, this increases the amount of time, as well as the size of the DIFFERENTIAL backups.  Eventually, it becomes beneficial to do a FULL backup, generating a new differential base.

  When restoring,  the latest DIFFERENTIAL backup contains all the changes made to the database since the differential base (FULL backup).  The FULL backup is restored first, followed by the latest DIFFERENTIAL backup and then any subsequent TRANSACTION LOG backups made after the just restored DIFFERENTIAL backup.   The DIFFERENTIAL or  TRANSACTION LOG backups between the FULL backup (differential baseline) and the last DIFFERENTIAL backup can be skipped, as the last DIFFERENTIAL backup has these database changes.

• TRANSACTION LOG Backup: Are only applicable to databases in FULL or BULK-LOGGED recovery modes.  When the transaction log is backed up, the active part of the transaction log is backed up which includes all log records not backed up in a previous TRANSACTION LOG Backup.

  When restoring the database, the FULL Backup is restored first, then if there are any DIFFERENTIAL Backups, the latest  is restored next.  Finally, all TRANSACTION LOG Backups completed after the FULL or DIFFERENTIAL Backup are restored. 

  It is important to note, that the TRANSACTION LOG backups must be restored in the order that they were taken, and none can be skipped. Finally, for databases in FULL or BULK-LOGGED  recovery, transaction log backups are necessary, not only to decrease the amount of potential data lose, but are the only way that the transaction log can reclaim used log space and control the growth of the  transaction log.

Additional information regarding SQL Server backups can be found in the this TechNet Article

[2-3] Restore FULL, DIFF, TLOG

• Restore the FULL, DIFFERENTIAL, and TLOG3 backups of the database (from steps 1-1, 1-3, 1-4 above)
• Select all data from your table to see where you stand.

-- FULL Restore followed by DIFFERENTIAL and TLOG....
USE master;
GO
DROP DATABASE Testing;
GO

RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH NORECOVERY;
     
GO
RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\TestingDIFF2.bak'
      WITH NORECOVERY;
GO
RESTORE LOG Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\TestingLOG3.bak'
      WITH RECOVERY;
GO

USE Testing;
GO

SELECT * FROM T1;
GO

And checking the results, we see that we’ve only restored 24 of the original 30 rows.

The reason that we don’t see rows 25 – 30, is because they were inserted to the table, after the TRANSACTION LOG backup to TestingLog3.bak occurred.  So, anything added after the last transaction log backup is not backed up – therefore, not restored

Assignment completed…