Chapter 6 Copy Only Backup

Both differential and transaction log backups need a full backup to be taken first. The 3 types of backups are linked in a known sequence. Taking a full backup resets this sequence, this can cause problems if other people don’t know about the latest full backup. A copy backup allows you to take a full copy of the database without disrupting this sequence. The authors helpfully call this ‘a backup that never happened’.

Step-by-step instructions on how to perform a copy backup using the GUI and T-SQL code is provided. Similarly, step-by-step instructions on how to perform a restore from a copy backup using the GUI and T-SQL code is provided.

Chapter 7 File and Filegroup

Very Large Databases (VLDB) often result in slow performance and long running maintenance jobs (e.g. defragmentation). Typical solutions to this problem include partitioning (splitting the table vertically), and use of filegroups. Filegroups allow you to store related data on a named filegroup. Backups and restores can be done for a given filegroup, this is typically faster than a full restore, and allows processing to continue on unaffected sections of the database, after a crash.

Step-by-step instructions on how to create, backup, and restore filegroups, using the GUI and T-SQL code are provided. Older data, that is less likely to be queried, can be put on a filegroup that has slower (and cheaper) access.

This is another useful chapter, providing more backup and restore options. Perhaps page-level restores could have been mentioned. Figure 7.7 is too small to read.

Chapter 8 Backing Up System Databases

In addition to application databases, it is important to regularly backup the various system databases, since these contain data needed for application databases to function.

T-SQL code is provided to restore the following: 

• master database – this contains logon information etc

• msdb database – this contains SQL Agent jobs etc

• model database – this is the template used when creating application databases

It is not possible to backup the tempdb database, since this is recreated anew when SQL Server restarts. It’s important to note that these databases need to be restored to a Server having the same version of SQL Server as the backup.

Chapter 9 Additional Best Practices

The chapter opens with a helpful analogy on the importance of maintenance to the smooth running of a car. Similarly, various best practices will ensure your restores and backups are optimal.

The chapter opens with a look at DBCC CHECKDB, this feature performs many low level checks to ensure your database is not corrupt. It is important to regularly perform this check, on large databases this is often done on the weekend.

When the database needs more space it grows, by default the new space is filled with zeros, this can take time, and can cause clients to time out. It is possible to use Instant File Initialization to allow the file to grow without filling it with zeros (note: some might regard this as a potential security risk).

This is a very interesting and useful chapter, but I feel so much more could have been said, e.g. the use of CHECKSUM as the page_verify_option to identify problems sooner, mention again the verify option on the backup, investigate the virtual log files to improve performance, look inside msdb.dbo.supect_pages for details of corruption errors, run DBCC CHECKDB on a restore on a non-production box, have document with steps/screenshots of the restore steps etc

Chapter 10 Encryption

SQL Server 2014 is the first version to natively provide encrypted backups. The chapter opens with a look at the prerequisites for encryption. This is followed by step-by-step instructions on how to backup with encryption, using both the GUI and T-SQL code. Finally, details on how to restore an encrypted database using T-SQL code is provided.

This chapter provide a good overview of how encryption is undertaken. Usefully the Adventureworks database is used in the examples, so will be helpful if you want to follow along.

Chapter 11 Back Up to Microsoft Azure Storage

Increasing the cloud is being used in software systems. Using the cloud for backup and recovery has the advantage of providing a different geographical location, should disaster strike your data centre.

This chapter provides plenty of step-by-step walkthroughs and screenshots detailing the preparation work needed to set up Azure storage, and how to configure your SQL Server credential. This is followed by step-by-step walkthroughs of backing up to Azure storage using both the GUI and T-SQL code. This is followed by restoring via the GUI and T-SQL code.

Conclusion

This book aims to give you a strong understanding of SQL Server’s backup and restore functionality. It is generally: easy to read, has useful step-by-step walkthroughs, helpful diagrams, and good links between chapters. The end of each chapter contains a helpful summary, points to ponder, and a review quiz. Much of the material is applicable to earlier versions of SQL Server too. The book is relatively cheap in price, so cost should not be a consideration in understanding this important topic.

This small book contains good base material, but much more information could have been provided, especially if you want a strong understanding. The book reads much better if you already know the subject matter. Parts of Chapter 1 should be rewritten.

Overall, this is an instructive overview of SQL Server’s backup and restore options, a very important topic.