SQL Server Backup Strategies

SQL Server - Backup Strategies

SQL Server has a number of backup strategies revolving around Full backups, Differential Backups and Transaction Log backups.

Full Backups

These do exactly what they say … take a full backup of the database.

Differential Backups

A differential backup includes only the data that has changed since the Full backup.

Transaction Log backups

These backup the transaction logs which are stored independently of the data.

A backup strategy can include all of the above...

Depending on data volumes, a full backup may be run daily, weekly or monthly. Any database restore will be based on the last full backup, so the more frequent the better, SQL Server - Backup Strategyproviding it does not impact on performance and is within storage limits. If only full backups are used in a Simple recovery model, then data loss in the event of failure is everything since the last Full backup.

Using Differential backups allows for smaller intermediate backups. A simple backup strategy using differential backups requires the last full backup and the latest differential backup in order to restore the database. Data loss in the event of failure is everything since the last differential backup.

NOTE: both the above strategies use the ‘Simple Recovery Model’, transaction logs are cleared down (truncated) regularly and automatically and cannot be used to restore the database.

Using the ‘Full Recovery Model’ requires Transaction Log backups as the transaction log will only be truncated when a transaction log backup is performed. A backup strategy in the full recovery model can be used with or without differential backups. To restore a database requires the last full backup, the latest differential backup (if one exists) and all transaction log backups since the last data back (full or differential). In the event of a failure, it may also be possible to backup the tail of the log (the portion of log that exists since the last transaction log backup), if so, recovery can be made up to the point of failure, otherwise recovery is up to the last transaction log backup.

In SQL Server it is possible to schedule the above using Maintenance plans.

 

Follow the Wizard to create Maintenance plans to suit your desired backup strategy.

NOTE: Always do a test restore to a test database to ensure your backup works as expected.

Anti-virus

Anti-virus scanning of SQL Databases can seriously impact performance and so it is recommended that your virus scanner is setup not to scan database files. This can either be done by disabling scanning on the folders where the files reside or by disabling scanning of the datafile file types. The file types in question are:SQL Server - Anti-virus tips

*.mdf

*.ldf

*.ndf

 

It would also be worth excluding the backup files too ….

*.bak (by default).

*.TRN (for transaction log backups)

 

I hope you found this article useful, I will be back with another SQL Server article soon.