Database Backups

Do you make a backup of backup or just the backup? Raise your hand if you don't make a backup of your laptop regularly! I make a backup copy of my phone every time I upgrade to a newer version of OS which I learnt the hard way. Backup is a process of making a copy of your documents, pictures, software, files to preserve the copy just in case there's a failure of hardware, software or other disasters.

We all should make a backup copy of the documents from our laptop, PC, and phone to a reliable external sources, yes more than one sources. This is done to preserve the copy just in-case the laptop got stolen or crash. Similarly, in an organization, database backup is done regularly by DBA team to preserve the data against catastrophe. The frequency of a backup and the types of backup depends on the business need of an organization. With this article, I will discuss about the database backup types, SQL Server backup options, backup exercise, backup compression and backup copy validation.

There are 3 main database backup options available. Let's discuss each options in detail.

Full Backup: As the name says, this backup copies the entire copy of data from a database. Full backup brings a peace of mind, but at the same time, it is resource intensive to execute with huge database. You will also need bigger source to store which might be costly to both store and restore if you are to run full backup daily.

Incremental Backup: Running full backup daily is an expensive operations, therefore we have other backup options available. Incremental backup copies only data that got added or update after a full backup. This backup prevents the need to store duplicate copies of unchanged data. If you run full backup every Sunday after midnight then you will need to run increment backup rest of the week to capture all the changes that happened after a full backup. This is much faster which runs without much overhead to database and also requires less storage space. To restore data, you will need to restore a full backup and all the incremental backups.

Differential backup: This backup copies all the file that have changed since the last full backup. The data restore from this backup is much faster compared to incremental backup. The only disadvantage is that the size of differential database grows bigger if you run this backup. You will need full backup and latest differential backup copy to restore.

SQL Server Backup Types: SQL Server will let you take Full, Differential and Transaction log backup. You can run these backups using T-SQL or by using the wizard from SSMS.

Full: A full backup contains all the data in a specific database or set of groups or files and also enough log to allow for recovering that data. It is the base for both Differential and Transaction log backup. Full backup is available on all three recovery models Simple, Bulk-Logged and Full.

Differential Backup: A differential backup depends on a Full backup. You have to have a full backup before you can create a differential backup. This backup copies all the files that have changed since the last full backup. This is must faster and requires less space compared to full backup. To restore you will need a full backup and the latest differential backup file.

Transaction Log Backup: Only a Full or Bulk-Logged recovery model lets you take the Transaction Log backup. The full backup along with transaction log backup will let you do the Point In Time (PIT) recovery of the database. Transaction log backup truncates the transaction log after a backup which prevents log filling the disk space. Transaction log backup is not available on Simple recovery model but the transaction log files are under control. How does that happen? Transaction logs truncation occurs atomically after a checkpoint run.

SQL Server Full Backup Script:

------------------------------------------- 
---Database Backup & Verify Restore --- 
-------------------------------------------- 
USE [master] 
BACKUP DATABASE [test_db] TO DISK = 
N'\\Shared_S\database_refresh\phonepbook\Backup\phonebook_backup.trn'
WITH noformat, init, NAME = N'test_db-Full Database Backup', skip, norewind, 
nounload, stats = 10 
go 
DECLARE @backupSetId AS INT 
SELECT @backupSetId = position 
FROM   msdb..backupset 
WHERE  database_name = N'phonebook' 
       AND backup_set_id = (SELECT Max(backup_set_id) 
                            FROM   msdb..backupset 
                            WHERE  database_name = N'phonebook') 

IF @backupSetId IS NULL 
  BEGIN 
      RAISERROR( 
      N'Verify failed. Backup information for database ''phonebook'' not found.', 
      16, 
      1) 
  END 

RESTORE verifyonly FROM DISK = 
N'\\Shared_S\database_refresh\phonepbook\Backup\phonebook_backup.trn'
WITH FILE = @backupSetId, nounload, norewind 

Here the script:

1. Makes a full backup of phonebook database.
2. Stores the backup on shared network drive
3. Verifies if the backup is restore-able without restoring it
4. Use Compression option if available (small file size & faster restore)

Backup Strategies Exercise: You work for a mid sized company supporting 3 productions SQL Servers Instances. The company collects data for forecasting and modelling. Data is so valuable that the company can't afford to lose data more than 15 minutes. The application gathers data. How would you plan a backup for these database where you can't loose more than last 15 min worth of data? Also, consider faster recovery time and less disk space to store while implementation backup solutions.

Option 1:

1. Set database to Full Recovery Model
2. Schedule Full backup daily after midnight.
3. Run a Transaction Log backup every 15 min.

Option 2:

1. Set database to Full Recovery Model
2. Schedule Full backup once a week
3. Schedule Differential backup daily
4. Run a Transaction Log backups every 15 min.

Which of the above options would you pick and why? Can you think any backup scenario that can beat these scenarios? 

Backup Compression: SQL Sever Enterprise Edition 2008 and later version lets you compress the database backup. The feature is highly recommend to use while backing up database. This saves the disk space and restores data faster as it has read less data. It should be used with care as it is resource intensive process.  Since compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore increase backup speed significantly. The only drawback of backup compression is the increase in CPU usage.  The additional CPU consumed by the compression process might adversely impact your database therefore you need to set up a resource governor and create a low priority on compressed backups session.

RESTORE VERIFYONLY: This is another important feature that verifies the backup copy without actually restoring it and also checks to see that the backup set is completed and the entire backup is readable. A good DBA uses RESTORE VERIFYONLY but the RockStar DBA actually restores data to verify it along with restore verifyonly command.

Backup can be done using SQL Server Management Studio or using script on demand. You can schedule to run your script or set up backup of all the database from Maintenance Plan and send alert and notification with status.

Recommendation: Be paranoid while taking and testing your backups. Data is important to any organization, without data the company is nothing. It is a job of a DBA to protect these data against failures. If all else fails, backup is the only way. Make a backup, test a backup, and protect your backup. There are tons of third party software that manage your backups, I personally like SQL Safe from Idera and I recommend it to anyone who is managing enterprise databases.