Authors: Tim Radney and John Sterrett
Publisher: Linchpin People Press
Pages: 166
ISBN: 978-1502573896
Print: 150257389X
Audience: New and mid-level DBAs
Rating: 4
Reviewer: Ian Stirk

This book aims to give you a strong understanding of SQL Server’s backup and restore functionality, how does it fare?

This is a short book, consisting of around 150 pages, divided into 11 chapters. This book is aimed at the beginner or mid-level DBA, and assumes only some basic T-SQL skills.

Backup and recovery are the most important topics for many DBAs, so it’s paramount to have an excellent understanding of them. Being involved in a recovery after a crash can be a stressful event, so it makes sense to learn and practise the subject under less troublesome circumstances (ideally using an up-to-date step-by-step document that anyone with an understanding of SQL Server could implement).

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1 SQL Server Database Files

This chapter opens with at look at the underlying files that make up a SQL Server database i.e. a primary data file (.MDF), zero or more secondary data files (.NDF), and a log file (.LDF). Data is stored in 8K pages, and 8 pages make up an extent.

The chapter then discusses, in outline, the different types of backup: 

• Full – backup of the whole database

• Differential – backup of the data changed since the last full backup

• Copy – backup of the whole database, without interfering with the backup strategy

• Transaction log – backup of transactions since the last transaction log backup 

Each chapter ends with useful summary, points to ponder, and review quiz sections. There are useful diagrams and links to other chapters in the book.

I had problems with several aspects of this chapter, which is unfortunate because the rest of the book mostly doesn’t have these problems. Problems included: 

• Uses terms before they are defined, including unnecessary material, some awkward analogies, and some bad logic/arguments

• p11 talks about moving a row from the leads table to the customer table, later p26 suggests a row in the leads table can convert from ‘new’ to ‘customer’ (in leads table) - this information is unnecessarily confusing, it would be better to omit it

• Giving a File Backup as a type of SQL Server backup, this is confusing, and unnecessary 

Maybe it would have been better to stick to the point, but don’t be put off, the remainder of the book is clearer.

Chapter 2 Performing a Full SQL Server Backup

This chapter opens with step-by-step instruction on how to perform a full backup using the GUI. Next the compression option is discussed, this provides a faster and smaller backup. The chapter then looks at T-SQL code for a full backup, this is often the preferred mechanism, since there’s less chance of omitting an option. The use of the STATS option to give feedback on progress is discussed. The chapter ends with a look at using both the GUI and comparable T-SQL code to perform a restore. It’s noted that you might want to change the file paths etc when you perform a restore.

This was a useful chapter, easy to read, with helpful explanations, diagrams, walkthroughs, and SQL code. There’s a useful point made about the need to test the backups are valid. The ‘verify backup’ option provides some validation. It’s stated the only way to fully validate that your backups are good is to restore them – however this is not completely true, it is possible to restore a corrupt database, a DBCC CHECKDB is needed to ensure a backup is good. There’s a useful point about including the date in the name of the backup as MMYYYYDD – however I would find YYYYMMDD easier for sorting.

The book’s subheadings relating to restores are explicit e.g. ‘Using the GUI to restore’, while the subheadings relating to backups are not e.g. ‘Using the GUI’. The subheadings should be consistent, this problem occurs in several chapters.

Chapter 3 Differential SQL Server Backups

This chapter opens with step-by-step instruction on how to perform a differential backup using the GUI. The chapter then looks at T-SQL code for a differential backup. The chapter ends with a look at using both the GUI and comparable T-SQL code to perform a restore – this requires a restore of the full backup, and then the latest differential backup.

Often a full backup is performed on the weekend, and a smaller/faster differential taken at the end of each working day. The differential backup becomes larger each day, as more data is changed throughout the week.

This was a useful chapter, easy to read, with helpful explanations, diagrams, walkthroughs, and SQL code. The interplay of the full and differential backups is explained well with the aid of diagrams.  

Chapter 4 Transaction Log Backups

Changes made to the database are recorded in the transaction log. The Log Sequence Number (LSN) records details of the change into the log file, this allows for point-in-time restores. The database recovery model needs to be FULL or BULK_LOGGED to back up the log file (SIMPLE mode means the log is provided only as a means to rollback/commit the current transactions, if necessary).

To restore, the latest full backup needs to be restored, then the latest differential backup (if any) applied, and then all the transaction logs since the differential backup or the full backup. Step-by-step instructions are provided on how to perform a transaction log backup using the GUI and T-SQL code. This is followed by using the GUI and T-SQL code to perform a restore.

Overall this was a useful chapter. A very good point is made about practicing and documenting the restore process – it’s much better to do this outside the often stressful real-life crash situations. Perhaps it should be explicitly stated that a transaction backup and database backup are separate events, you typically perform both. I was surprised that some of the more common log file problems (e.g. full log, log never back up etc) were not discussed. Similarly, point-in-time restores can make use of the STOPAT command, this was not discussed. Maybe marked transactions could also have been discussed.

Chapter 5 Common Restore Strategies

The chapter opens with a short discussion on the importance of Service Level Agreements (SLAs), these should form the base of your backup and restore strategy. The SLAs should include details of the Restore Time Objective (RTO) - how long the database can be unavailable, and Restore Point Objective (RPO) - how much data can you afford to lose.

The chapter continues with a useful look at the common restore strategies: 

• Full backup only

• Full backup with differential backups

• Full backup with transaction log backups 

Some very useful SQL code is provided to retrieve details of the backup history from the msdb database (i.e. what types of backups were taken and when). There’s an excellent point that when a backup is taken, its associated restore script should also be produced.

In many ways, the backup strategy should be determined by the (ease of) the restore strategy, this point should have been given greater emphasis.