SQL Server Backups – Simple Guide Part 1

Introduction

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)

BACKUP DATABASE Prod TO DISK = 'FILESERVER1BACKUPSProd.bak'

 

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)

DECLARE @date VARCHAR(128)
SELECT @date = CONVERT(CHAR(128), GETDATE(), 120)
SELECT @date = 'C:BackupsProd_'+ REPLACE(@date, ':', '_') + '.trn'
BACKUP LOG Prod TO DISK = @date

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

 


ABW Milestone 7 UPDATE 01

Many organisations that use UNIT4 Business World will wait until the first update has been released before they consider upgrading.  Well UPDATE 1 for ABW Milestone 7, or to give it it’s formal name – UNIT4 Business World On! Spring 2017 Update 1 is now available for download.

https://abwupdates.agresso.com/

We have carried out several test Milestone 7 upgrades over the last few months, with two customers that upgraded from Milestone 3 and Milestone 4 going Live on Milestone 7 prior to the update 1 release.


U4BW Supported Operating System & SQL Server Versions

U4BW – Windows and SQL Server Versions

UNIT4 Business World (Agresso) is now supported on Windows Server 2016 and SQL Server 2016 which can offer enhanced performance, security and high availability features such as SQL Server Availability Groups.

SQL Server High Availability Groups  were first introduced in SQL Server 2012 as an Enterprise Feature. In SQL Server 2016 Standard Edition, Basic Availability Groups are now available.

Basic Availability Groups are a cut-down version of the Enterprise offering. They do however fit quite well with the U4BW setup and can be used to provide a very good High Availability / Disaster Recovery setup for a relatively low cost.

The setup can be:-

  • Both servers on-premise
  • Both servers cloud based
  • Primary server on-premise and secondary server cloud based (Hybrid Cloud)

In each setup there are advantages and disadvantages. But each one offers a great solution compared to a single server.

The following table shows which versions of U4BW are supported on Windows Server 2016 and SQL Server 2016. (Please note, SQL Server 2016 doesn’t need Windows Server 2016 and will run on Windows Server 2012 R2.)

U4BW Version SQL Server 2016 Windows Server 2016
Milestone 5 Yes Requires UPDATE05
Milestone 6 Yes Requires UDPATE03
Milestone 7 Yes Yes

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….

https://www.microsoft.com/en-gb/server-cloud/products/sql-server-2005/

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