Skip to the content

SQL Server Backup Strategies

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.

Tom Jenkins

Tom Jenkins is the Managing Director at Dynamics Consultants. As well as having a strong product knowledge, his strategic knowledge of industry and the business landscape helps him to provide thought leadership to a range of vertical sectors. 

comments powered by Disqus

I find their approach to our relationship very professional whilst being refreshingly realistic. We now consider them to be part of our team

Lee Crowhurst, Technical Director

Partner with the Experts

With over 200 years of combined experience in Microsoft business solutions, our team will help to get you up and running, as well as building a partnership that keeps you supported, all from our UK offices. 

From functionality and licencing to business strategy, we like your questions; contact our experienced team for open, honest and reliable advice so that we can find the answers.