Backups by Celerity Technical Consultant, Mike Metcalf

Backups Posted on: 20/06/13 In this article we’ll take a look at the differences in Backup and Restore methodologies between on premise ‘SQL Server’ and cloud based ‘Azure SQL Database’. Whilst both products are based on a similar set of technologies under the hood, they are very different animals in the way that they work and require different skills to administer them correctly. SQL Server Backups Traditional on premise SQL Server has three fairly well established backup methods that are generally used in combination to ensure that should a failure happen, a database can be restored to any point in time that is required. In all cases the database backup in transactionally consistent from the point in which it was taken. Full Backups This essentially takes a complete backup of the entire database and stores it in a file on disk. Generally the taking of a Full Backup would be seen as a weekly event. This is primarily because maintaining multiple copies of the entire database would require a significant amount of storage to do so. By way of example, if your company had a retention policy of 5 days, a 100 GB database would require 6 times the storage needed for just the database (original + 5 copies = 600 GB). Task Storage Required (GB) Full Backup 1 100 Full Backup 2 100 Full Backup 3 100 Full Backup 4 100 Full Backup 5 100 Total Storage Required 500 To recover the database you would simply restore the full backup file required. Differential Backups A Differential Backup, backs up all data written to the database since the last full backup was taken. Each differential backup will be larger than the previous one, but only by the amount of data written to it. Using the same 5 day retention period from above, and assuming that your database is growing by 1GB per day this gives the following storage profile. Task Storage Required (GB) Notes Full Backup 100 Differential Backup 1 1 Differential Backup 2 2 (Diff 1 + 1GB) Differential Backup 3 3 (Diff 2 + 1GB) Differential Backup 4 4 (Diff 3 + 1GB) Total Storage Required 104 As you can see, this represents a significantly smaller backup footprint than would be required in the Full Backup only scenario (204 GB as opposed to 500 GB) whilst achieving the same goal. In order to recover the database in this situation, you would restore the Full Backup, and then the latest Differential Backup required. Transaction Log Backups SQL Server maintains a log of all activity written to the database in the precise order that it was written in, called the transaction log. Backing up the transaction log means that it is possible to restore the database to a specific point in time throughout the day. Generally speaking, multiple transaction log backups would be taken periodically throughout the day. They are generally very small as they only back up the data since the last transaction log backup. It should be noted that in order to take transaction log backups, it is necessary to run the database in ‘Full’ recovery mode. Running a database in ‘Simple’ recovery mode effectively means that the transaction log is truncated once the transactions have been committed to the database, meaning that since it has been cleared out, it cannot be backed up. It is not essential to run a database in ‘Full’ recovery mode, but it should be remembered that if you do not you will only be able to restore the database to the last full or differential backup that was taken. All work on the database since that point will have been lost. Transaction log backups are used in conjunction with Full and Differential backups. In order to recover the database to a point in time you have 2 options available: Option 1 Restore the Full Backup and then restore each transaction log backup in turn. As you can imagine, if you take 12 transaction log backups throughout the day this can mean that you have a lot of files that will need to be restored. Option 2 Restore the Full Backup, then restore the latest Differential Backup, followed by only the transaction log backups taken since the differential. This significantly reduces the number of files that need to be restored in the correct order. The more observant of you may have noticed that since we are taking full and transaction log backups, that it is not strictly essential to also take differential backups, and you would be right, this is perfectly true, but seeing as I’m a ‘belt-and-braces’ type of guy who likes having different options available when it comes to recovery, that is the way that I always work. Windows Azure SQL Database Backups Windows Azure SQL Database is Microsoft’s cloud based offering for SQL Server. Whilst it is based on the same technology it is quite a different in the way that it is managed and administered. These differences also extend to the way in which backups are taken. Microsoft has two recommended methods for backing up an Azure SQL Database: Database Copy This feature essentially creates a new database that is transactionaly consistent with the source database that it was copied from at the time that the copy process completes. With a large database, this copy operation can take a while to run, but you know that all changes made to the source database during the copy process are also going to be committed in the backup. A word of warning, whilst this does ensure transactional consistency, it also means that you have created a second database and, therefore, have incurred any additional costs associated with that action. You should also keep in mind that this method does not actually allow you to create/keep a copy of the backup on your own servers as it leaves the database copy on the Azure platform. SQL Database Export Service The database export service copies the object definitions (tables, stored procedures, etc.) from the source database to a logical file (BACPAC). This is then followed by a bulk copy of all data from the tables into the BACPAC file. Transactional consistency in this case is potentially a problem, changes made to the source database that affect only certain tables may not be included in the BACPAC file. The only way to guarantee transactional consistency within a BACPAC file is to first put the database into read-only mode (preventing it from being written to) followed by reverting back to allow writes once the backup has finished. This has obvious questions regarding system availability and by itself is not a valid solution for larger/enterprise systems that need to run 24/7. In order to ensure transactional consistency, and allow you to maintain a copy of the database on your local server(s) for Disaster Recovery purposes, you will need to use both of these methods. First create a transactionally consistent copy of the production database using method 1, and then create a BACPAC backup of the copy using method 2. I hope that this brief overview has helped you to see that regardless of which version of SQL you choose to use, the backup mechanisms in place are robust enough to provide a level of restore needed for each platform. Celerity Consultants have a wealth of experience with Microsoft products and would be happy to discuss any requirements that you may have. Mike Metcalf, Technical Consultant, Celerity Limited Contact - See more at: http://www.celerity-uk.com/news/194/backups#sthash.Ex5jerl2.dpuf