SQL Server Database Backups

Backups are crucial. You need to have the right backups in place to have your data covered in the case of any disasters. In this post I go through the different backup types and some of the different strategies for implementing them.

Recovery Models

The first thing to decide on before the backup type, is the Recovery Model. You set the Recovery Model type for each database. The Recovery Model is what decides what data to keep in the transaction log and for how long

1. Simple:  the transaction is written to the transaction log, but as soon as the transaction is complete and the data has been written to file, the space that was used in the transaction log is now reusable by new transactions. This  means you wont be able to do point in time recovery, you can only restore to your last Full or Differential backup
2. Full:  This is basically us telling SQL Server to leave the transaction log alone. It puts the responsibility on us to make sure the transaction log is emptied. It is emptied when you do a transaction log backup.If you aren’t backing up the transaction log it can fill up. This model allows you to do a point-in-time restore
3. Bulk Logged: this is for when you are doing bulk operations and you want to minimize the amount of logging during that operation. You cant do point in time recovery with this model as it doesn’t have enough information recorded.

Backup Types

1. Full Backups take a complete copy of the database and allow for a complete restore to a certain point in time. The good side of these is they backup everything, bad side is they take the longest.
2. Differential Backups allow to take a snapshot of all the data that has changed since the last full backup.  These backups are cumulative, if you have multiple differential backups they will have all the changes that were included in the previous differential backups. EG: if you do a full backup on Sunday, then a differential backup each weekday, Friday will include all the changes that happened on Mon, Tue, Wed, Thurs. and Friday.
3. Transaction Log backups  include data that has been modified since last full, differential or T-log backup. These are not cumulative because every time a transaction log backup occurs it truncates the log, which gets rid of all the inactive or old transactions  that have been committed to the database. Transaction log backups give us point in time recovery.
   The transaction log is a log of all the transactions that have occurred since the last truncation. Anytime a transaction modifies data it occurs first in the log, then when SQL server gets around to committing that modification to the database data, that entry in the transaction log is marked as inactive and the next time a truncation occurs it will get rid of that record.

Factors to consider when planning the backup strategy 

• Acceptable data loss: is it a day? An hour? 5 mins? If it’s a day you could away with full and differential backups. If its down to minutes then you probably need transaction log backups
• Type of database: if you have a database that has lots of transactions and low acceptable amount of data loss you do something like:
  1 x nightly full backup
  hourly differential backup
  10 minutely transaction log backups.
  This is a good strategy for a small to medium size database where acceptable data loss is around 10 mins.
• Size of database: if the database is very big you wouldn’t be able to have the same setup as we had in the example above as it would take way to long. In this case you might do:
  full backup x 1 a week
  differential x 2  a day
  Transaction log x 1 an hour
  If you had an exceptionally large database, you split the backups into filegroups. Most large databases have a of lot of archive data that is read only. You can put this into its own filegroup and set that filegroup to backup once a month.
  Then put the data that has lots of action on it into its own filegroup and back it up more often
• Restoration Time: this is a factor for businesses that don’t implement a high availability or mirroring option. When their system goes down they are going to have to do a full restore. NOTE: transaction logs take the longest to restore, and full backups are the quickest to restore. This is because transaction logs almost have to go through statement by statement. 

The restore process goes like this:

1. Restore the last created full backup
2. Restore the last created differential backups
3. Restore the transaction log backups in sequence (because it truncates the logs you need them all to complete the restore). If for any reason one of the log backups was corrupted or couldn’t be restored, this means the chain is broken and you can only restore to the log backup before the chain was broken.

Backing Up Databases

Full Backups

How to run a Full database backup using the interface:

1. Right click on the database and go to ‘Tasks -> Backup’
2. Select ‘Full’ in the backup type drop down menu
3. In the ‘Destination’ box you select where you want to save the backup to. If you don’t want it in the default area, click ‘Remove’ and then ‘Add’ to select your location of choice.
   NOTE: you add multiple destinations and it will split the database backup file across these destinations. If you have a very large database you could select 3 locations across 3 drives, this would be a big performance improvement when running the backup
4. Click OK to run the Full backup

How to run a basic Full database backup using T-SQL:

1. Run the following code in a query window. Make sure to change the name of the database and the location you want to save the backup to-

BACKUP DATABASE [AdventureWorks2012] 
    TO DISK = ‘E:\msft_sql_70_462\13-Backup\AdventureWorks2012_FULL.bak’      WITH NAME = ‘AdventureWorks2012-Full Database Backup’ 
GO 

Differential Backups

How to run a differential backup through the interface:

1. Right click on the database and go to ‘Tasks -> Backup’
2. Select ‘Differential’ in the backup type drop down menu
3. In the ‘Destination’ box you select where you want to save the backup to. If you don’t want it in the default area, click ‘Remove’ and then ‘Add’ to select your location of choice.
4. Click OK to run the Full backup

How to run a Differential database backup using T-SQL:

1. Run the following code in a query window. Make sure to change the name of the database and the location you want to save the backup to-
   (you may notice it is very similar to the full backup T-SQL code except for the ‘DIFFERENTIAL’ flag at the end)

BACKUP DATABASE [AdventureWorks2012]
    TO DISK = ‘E:\msft_sql_70_462\13-Backup\AdventureWorks2012_DIFF.bak’       WITH NAME = ‘AdventureWorks2012-Full Database Backup’, DIFFERENTIAL
GO 

Transaction Log Backups

How to run a Transaction Log backup through the interface:

1. Right click on the database and go to ‘Tasks -> Backup’
2. Select ‘transaction’ in the backup type drop down menu
3. In the ‘Destination’ box you select where you want to save the backup to. If you don’t want it in the default area, click ‘Remove’ and then ‘Add’ to select your location of choice.
4. Click OK to run the Full backup

How to run a Transaction Log backup using T-SQL:

BACKUP LOG [AdventureWorks2012]  
    TO DISK = ‘E:\msft_sql_70_462\13-Backup\AdventureWorks2012_LOG.trn’ 
    WITH NAME = ‘AdventureWorks2012-Transaction Log Backup’ 
GO 

Log Backup Options

Truncate the Transaction Log: this means that it will leave anything that is active in the Transaction log, but anything that has been committed or written to disk will be truncated
Backup the Tail log: This will backup the active portion of the log

Automating The Backup Strategy

Using T-SQL:

You can do this using T-SQL and then setting up an SQL Agent job to run the code on a schedule. The following code will backup the AdventureWorks2012 database to the file path E:\Backups using the naming convention ‘AdventureWorks2012-DATEOFBACKUP_Full.bak

DECLARE @filePath VARCHAR(256)     — root path for backup files
DECLARE @fileDate VARCHAR(20)     — file date of backup
DECLARE @fileName VARCHAR(256)     — full path with name and date

  SET @filePath = ‘E:\Backups’ 
SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

SET @fileName = @filePath + ‘AdventureWorks2012’ + ‘_’ + @fileDate + ‘_FULL.bak’  

BACKUP DATABASE [AdventureWorks2012] 
    TO DISK = @fileName
    WITH NAME = ‘AdventureWorks2012-Full Database Backup’

GO

Using The Interface:

1. Expand ‘Management’
2. Right click on ‘Maintenance Plans’ and select ‘New Maintenance Plan Wizard’
3. Give the Plan  a name and select ‘Separate Schedules For Each Task’
4. If you have more than one instance you will need to select the instances you want to apply the plan to
5. In the Maintenance Tasks Window select the following:
   – Backup DAtabase (FULL)
   – Backup Database (Differential)
   – Backup Database (Transaction Log)
6. In the Full Backup task window select the Database you want to backup
7. Select location to save the backups. You can also put a tick in the box to “Create a sub-directory for each database” so it keeps all the backups in different folders
8. Repeat these steps for the Differential and Transaction Log backup pages
9. On the next page you can select to write a report on the maintenance plan each time it runs, and/or send an email with the results each time it runs. (See section on setting up Database email)
10. Click Next and then Finish to create the Maintenance Plan

Now that we have created the Maintenance Plan we need to set it to run on a schedule.

Setting the Schedule for this Maintenance Plan

1. Go to Maintenance Plans and double click the Plan you just created
2. You will see 3 subplans, 1 each for Full, Differential, Transaction Log backups. Click on the calendar icon to set the schedule.
   Its probably worth changing the name of the Subplan to something more relevant like ‘FULL, Differential or Log’
   For the Full backup task we will set it to run once a week on Sunday at 12am
3. Then do the same thing with the Differential subplan, setting the schedule for daily at  Midnight
4. Then for the Transaction Log sub plan set the schedule to run daily and set the Daily Frequency to every 30 mins
5. Save the Maintenance Plan and it is now ready to go

Copy Only Backups

As stated earlier, differential backups use the most recent full backup as their base., I.e.: they backup any changes made since the last full backup. Lets say someone does a full backup as a test, then deletes that backup. Now the differentials are backing up since that last test backup was done, but you don’t have the full backup anymore and therefore the sequence is broken and you lose data. If you try to restore this Diff backup to the Full backup you have you will get an error.
Copy only backups don’t affect the sequence.  The differential backups still work from the last full backup, not the copy only backup.

To run a copy only backup using T-SQL:

BACKUP DATABASE [AdventureWorks2012]
    TO DISK = ‘E:SQLBackup\AdventureWorks_Copy.bak’ 
    WITH NAME = ‘AdventureWorks2012’, COPY_ONLY 
GO

It’s the ‘COPY_ONLY’ flag that makes it a copy only backup.

To run a copy only backup using the interface:

1. Right click on the database and go to ‘Tasks -> Backup’
2. Select ‘Full’ in the backup type drop down menu
3. Under Backup Type, select ‘Copy Only Backup’