SQL Backup – Data stored in SQL server database is prone to catastrophic loss. Any sort of technical or non-technical calamity can give rise to such catastrophic loss. To minimize such loss of data, there is a need to adopt effective preservation policies. Such preservation policies cannot be put forward without the enactment of effective backup and recovery strategy.
A well-planned backup and restore strategy is the key to save databases from the potential loss. To examine the effectiveness of these strategies, it is important to test them regularly. In this way, loopholes may get rectified and effectiveness remains intact. SQL Server Management Studio is an excellent tool to perform such activities.
Types of SQL Server
It must be noted that effective backup is the prerequisite for efficient recovery. Not every backup option is tailored to be effective for every SQL server. Vendors like Microsoft Azure, Google Drive and Amazon S3 offer storage services for the safe parking of these backups.
Following are some of the most common types of SQL backup
Full Backups
As the name implies, the full backup is tailored to backup everything. It is also considered to be the foundation of any backup job. It makes copies of all objects of the database, such as indexes, tables, functions, procedure and views.
A full backup creates a full backup of the database along with the transactional log. Since all contents have been backed up in a single iteration. Hence, it will be easy to restore SQL server database when required.
Differential Backups
As clear from the name itself, it will check for any difference since last full database backup and will be comprised of all those changes. Basically, differential backup will be small if minor changes have been identified since the last backup. Similarly, it will be large if considerable changes have been made since last backup.
Owing to its differential property, it is much faster than a full backup. With frequent differential backups, the size of backup will increase. This increase in size will eventually increase the time, that will be taken to restore a database. This is why it is recommended to perform full backup at regular intervals because the size of differential backup will not be large to the extent that its restoration becomes time taking.
Transaction Log Backup
Such backup of the database facilitates the transaction log’s backup. Transaction log backup provides a history of events, such as a event when data was modified. On account of this, it allows the data to be recovered up to a specific point in time.
Modifications are maintained and logged in the Log Sequence Number (LSN) in the log chain. This log chain starts with the full backup and continues until it is broken. Such discontinuation will occur only if the recovery model has been changed or extra full backup has been taken.
For instance, if you want to recover a database to a certain point in time, then you need to have a full backup along with the latest differential and transaction log backup. In this way database can be recovered to that specific time or closest to the occurrence of accident which resulted in data loss.
Tail Log Backup
In case of failure and before the initiation of the restoration process, the first line of action is taking the tail log backup of the transaction log. It is basically an intermediate step before the initiation of the restoration process. On account of this, it is also known as tail log backup restoration.
Recovery of SQL Server
For the restoration of SQL server data, there are two basic recovery models. These recovery models facilitate certain restoration scenarios. There are four possible scenarios which can be restored using simple and full/bulk-logged recovery models.
Restoration using Simple Recovery Model
Let’s see how the following possible scenarios can be restored using the simple recovery model:
Complete Database Restoration
There are two ways to restore the complete SQL server’s database. Firstly, complete restoration can be made by recovering a full database backup. Secondly, complete restoration can also be done by restoring a full database backup, followed by recovery of differential backup.
File Restoration
It is possible to restore at least one or more read-only files. However, this can be done only if database possesses minimum of one read-only filegroup.
Piecemeal Restoration
For piecemeal restoration it is necessary for the recovery to be done in stages at the filegroup level. Restoration will be initiated with the recovery of all read or write primary filegroups. After this, secondary filegroup’s read or write files will be recovered.
It must be noted that the simple recovery model does not offer page-level restoration.
Restoration using Full/Bulk-logged Recovery Model
Following literature intends to guide about the restoration of all four possible scenarios using full/bulk-logged recovery model:
Complete Database Restoration
It is the most common scenario of SQL backup server’s recovery. Here restoration is done by restoring a full database backup along with the differential backup and backup of all subsequent logs. The process is completed upon recovery and restoration of last log backup.
File Restoration
It is possible to restore one or more files rather than restoring the entire database. Usually, file restoration is done when the SQL server’s database is offline. However, some editions of SQL server also allow restoration to continue if database is online.
But, it must be remembered that the filegroup where subjected file is present must remain offline during restoration.
Page Restoration
One or more damaged pages can be restored using the full/bulk-logged recovery model. Restoration can continue to run irrespective of the database status to be online or offline. However, it is necessary for the subjected pages to stay offline during this process.
It is necessary for the unbroken chains of log backups to remain available throughout this process so that page can sync with the current log file.
Piecemeal Restoration
Restoration and recovery will be done in stages at the filegroup level. The process will start with the restoration and recovery of primary filegroup.
Irrespective of the way by which data is being restored and recovered, SQL Server Database Engine will make sure that the whole of the database remains logically consistent.
