SQL Server Backups – Simple Guide Part 1


This is a quick guide to the more popular SQL Server backup routines. It isn’t really intended for experienced professional DBA’s, but in many cases the DBA role of installing SQL Server and setting up backup routines is not actually carried out by DBA’s. But rather the responsibilities for this are managed by Server / Infrastructure Engineers or Developers.

Now we aren’t implying that people in these roles are not capable of backing up a database, but beyond the full backup schedule the waters can become a bit muddy in relation to when to use differential backups and / or transaction log backups.

Below is a summary of Full, Differential and Transaction Log backups. Part 2 of the blog will go through how to check what backups have taken place, backups carried out by third party products etc.


Full Backups

As the name implies a full backup backs up all of the database to a file, no great surprises there. If a conventional SQL Server backup routine is used as opposed to using third party backup software such as CommVault or Veam, then the database will be backed up to:-

  • Local disk on the database server
  • Network location such as a shared Windows folder. (The user running the SQL Server service will need write access to the shared folder)
  • tape device (although not sure how often databases are backed up directly to tape nowadays, it’s usually first to disk)
  • Azure backup location

The standard SQL Server backup file is a single file, normally with a .BAK extension. This file can then be restored over the top of the existing database or restored on another server that is running the same or later version of SQL Server.


Differential Backups

A differential backup or sometimes called incremental backup, works in the same way as the full backup, but it simply backs up all of the changes that have been made to the database since the last full backup took place.

For example, if a full backup is taken at 01:00 and then a differential backup at 09:00, the differential backup would contain the database changes that have been made in the last 8 hours. Likewise if a further differential backup is taken at 17:00, the backup file would contain the database changes that have occurred in the last 16 hours.

There is no relationship or dependency with the two differential backup files. So if you needed to restore the differential backup that was taken at 17:00 it would just be a case of restoring the full backup that was taken at 01:00 (with norecovery) and then restoring the 17:00 differential.

All differential backups have a relationship and dependency on the last full backup that was taken.


Transaction Log Backups

Transaction log backups can only be done when the database is in Full recovery mode. The other two modes are Bulk Logged which is rarely seen and Simple recovery which broadly speaking is usually used for test and development environments or other systems which would never require a point in time recovery.

The transaction log backups contain the changes that have been made to the system. But unlike differential backups the transaction log backups contain the changes that have been made since the previous transaction log backup.

Going back to the previous example of a full database backup taking place at 01:00. For the sake of illustration there is then a transaction log backup occurring at 02:00 and then again on an hourly basis until 17:00. Each backup would contain the changes to the system that have taken place in the previous hour.

If you wanted to restore the database to 17:00 you would restore the full backup (with norecovery), then restore each of the transaction logs in turn. All would be restored with norecovery except the last one which would be restored with recovery (the default setting).

Unlike differential backups, transaction log backups are dependent on each other as they form a chain. So if any of the transaction log backups were lost then the recovery would not be able to go any further.

Transaction logs also allow you to restore to a particular point in time (POITR). So for example if someone accidentally dropped or deleted from a table at 16:34, then the database could be restored on the same or different server to 16:33.


Backup Compression

In SQL 2008R2 Standard edition and later, all conventional SQL Server backups can be compressed. The compression ratio can vary depending on what type of data is being held in the database. Tables holding BLOB Images such as files or images will not compress as well as tables that hold text and numeric data. If you are lucky you could see the size of the backup file reduce to the region of 10 – 15% of the original backup size.

Compression can be selected as an option when the backup is done, or the default compression (is it On or Off) can be set at the SQL Server level and this setting will be used when no compression options are specified.


Command Line Backups

Although it is possible to carry out all of these commands by using the GUI within SSMS, once you are used to command line backups they are more convenient.

Simple full backup

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak'


Full backup using compression

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH compression


Backup to a network share (SQL Server Service Account needs write access to share)



Full backup which also overwrites any backups with the same name

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH compression, format


Full backup which also overwrites any backups with the same name and shows progress in 1% increments

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH compression, format, stats=1


Differential Backup

BACKUP DATABASE Prod TO DISK = 'C:BackupsProd.bak' WITH differential


Transaction Log Backup

BACKUP LOG Prod TO DISK = 'C:BackupsProd.trn'


Transaction Log Backup with Date and Time appended to filename (i.e. Prod_2017-10-16 13_49_35.trn)

SELECT @date = CONVERT(CHAR(128), GETDATE(), 120)
SELECT @date = 'C:BackupsProd_'+ REPLACE(@date, ':', '_') + '.trn'

Welcome to our new team member Bobby Balmer

Intersect Consulting would like to welcome on-board the newest member of our team Bobby Balmer.  Bobby has joined the company as a Junior DBA and after only one week with us, Bobby is already adding value to the team and the Company.  Bobby is a bright guy with great ideas and loads of initiative. We are all looking forward to working with him and wish him every success with Intersect Consulting.

Connect with Bobby on LinkedIn


Extended Support for SQL Server 2005 Ended in 2016

Extended support for SQL Server 2005 ended on 12th April 2016. This means that Microsoft are no longer producing security updates for this product. We have worked with Customers recently that still have some SQL Server 2005 instances.

SQL Server 2008 and 2008 R2 still have some life left in them with the extended support running until 2019, although mainstream support for them did stop in 2014. However, some software vendors have dropped support for 2008 and 2008 R2 for their latest software releases.

For further details….


If you need to upgrade your SQL Server instance, then we can help. Our cost effective SQL Server Technical Consultancy is delivered by experienced and certified SQL Server Experts.
Contact us on 01942 366730 or drop us an email a enquiries@intersectconsulting.co.uk

IR35, the Public Sector, Contractors & maintaining critical SQL Server systems

With the ongoing pressure on public sector organisations to cut expenditure and now the additional burden of the new IR35 rules (which come in to effect on 6th April 2017). The new IR35 rules stop contractors being paid via their limited company, which effectively forces them into a PAYE scheme as opposed to paying themselves largely in dividends. This rather large tax burden on many contractors will either force them to leave the Public Sector altogether and switch to the Private Sector or it will push up the daily rate so as to offset the additional tax costs they face.


It’s difficult to predict how many contractors will be affected by this issue or what the effects will be on Public Sector organisations. But there is a general expectation within I.T. departments that taking on experienced contractors for interim or project based work will become more difficult.
For some positions the role may be left empty and the responsibilities spread out to other team members. This also happens for roles where the volume of work is not great enough to justify a full time member of staff. We see this at a lot of organisations which operate critical SQL Server based systems, but have no experienced database administrator to manage them.


This leads to an approach of only maintaining systems when they stop working. Along with increased system downtime and degraded performance, there is also an additional burden on existing staff to carry out roles which they are not trained for.
Intersect Consulting provide a low cost service that manages and maintains your SQL Server Database environments on a daily basis and also provides an on-demand service when required.


Visit our website for further information at www.intersectconsulting.co.uk , several NHS foundations and Local Authorities are already benefiting from our services.


SQL Server Merge Replication – Server Migration

We were recently working with a customer that had a SQL Server merge replication topology involving several servers. One of the servers was a fairly old physical server which was running out of resources and disk space. A new physical server was on the network waiting to replace it. The initial plan by the development was to allow for a couple of days of downtime while the 400 GB + merge replicated database was re-initialized from a snapshot. Adding to the complexity of the migration were 39 linked servers, a large number of SQL Logins, over 90 SQL Server Agent jobs and 16 other user databases. Rather than re-initialize the subscription and manually migrate everything over, we decided to use an alternative solution.

These are the steps we took.

  • Install SQL Server on new server Ensure that the installation directory and the system databases directory match what is on the old server.
  • On the new server, shutdown SQL Server and do offline backups of the system databases (MDF and LDF files) then once complete start SQL Server
  • Backup the merge subscription database and copy the backup file over to new server
  • Restore merge subscription database on new server with NORECOVERY so that further transaction logs can be applied. Don’t use the KEEP_REPLICATION option at this time as you can’t use this option with NORECOVERY.
  • Backup transaction logs for merge subscription database and restore them on new server, again using the NORECOVERY option without the KEEP_REPLICATION
  • Stop SQL Server Agent on the old server and check that there are no connections to the subscription database before running the next step. If there are connections the next step will error.
  • Carry out final transaction log backup of the merge subscription database but use the NORECOVERY option when performing the backup. This will put the database on the old server into a RESTORING state.
  • Restore the final transaction log onto the new server, and again use NORECOVERY without the KEEP_REPLICATION option. At this point both the databases will be identical and both in RESTORING state.
  • Shutdown SQL Server on both servers.
  • Set the SQL Server service to be manual on both servers
  • Copy over all of the MDF and LDF files from old server to new server (with the exception of the merge subscription database). The drive letters and directory structures that they are copied to must be identical to the ones they were in on the old server. The system database files have to be copied over as well, they will replace the ones on the new server. (They have been backed up in a previous step).
  • Shutdown the old server. Optionally rename it just in case it ever gets started and brought back onto the network.
  • Rename the new server so that the hostname matches the old servers host name
  • Restart new server
  • Start up SQL Server on new server
  • Set SQL Server services back to Automatic startup
  • Verify SQL Server is online and that the SQL Error log contains no issues
  • Bring the subscription database back online with the following command
restore database dbname with recovery, keep_replication

Because the version of SQL Server had changed slightly when SQL Server was first brought online couldn’t access it as it said it was running upgrade scripts. Once they’d been run it was necessary to run a couple of procedures manually due to the subscriber being offline



Total downtime was around 20 minutes and replication picked up with no issues as soon as the database was brought back online.