Never lose track of your data: strategies of database restoration every remote DBA expert needs to know
If you are about to embark on a journey of database maintenance and management, you must have always wondered what distinguishes a good DBA from a great one. Is it the knowledge of the latest DBMS? Is it the ability to backup data whenever and wherever required? Or is it the ability to secure data from threats and attacks?
But we urge you to think something calmly — what is the point of creating sequences of perfectly timed backups if a DBA has no way to restore them? Any well-trained DBA can create strings of multiple backups to bring a database up to the point, but what is the real purpose of that backup if it cannot be accessed and restored during the times of crises?
As of now, there are three types of backups useful for the DBA that can be performed in Microsoft SQL Server –
i. Full backup
ii. Differential backup
iii. Transaction log backup
The other two kinds of backups include the Page Restores and the File/Filegroup Restores. In this article we will mainly focus on the full backup, differential backup and transaction log backup models that keeps databases updates and the data secure.
What are complete database restores?
After years of work and surveys in this field, we conclude that the great DBA is the one who can create workable and realistic strategies for restoration, so all data can be perfectly backed up till the point of failure. This is the reality in the case of a logged recovery model. Any DBA who uses this model (full or bulk-logged) can perform a series of backups dedicated to the complete restoration of the data right up to the point of the system failure.
If you want your restoration game to be on spot, you may want to start with applying your base database backup and then a differential backup followed by the transaction log backups till any point you choose. This point could be the end of the last transaction log backup or be bang in the middle of your last transaction log backup.
How do differential log backups help?
The base backup is necessary to build your platform for the restoration process. At this stage, you have differential log backups and transaction log backups to deal with. The differential log backups “remember” all the changes in the pages of the database since the last complete backup. The next example will help you understand the function of each backup better.
If you have a full backup scheduled at 11 am each Friday and your differential backup is scheduled at 10 am each day, then each daily differential backup will keep getting larger till the next full backup on the next Friday. Subsequently, if there’s a systems failure on a Monday, you are bound to restore last Friday’s complete backup followed by the differential backup from Monday 10 am. But, just in the case, you experience a database failure at 8 am on a Friday; you will have a little more work on your hands. You do not have the day’s differential breakup. Hence you will have to apply Friday’s full backup followed by the next differential backup you have in hand (Sunday). Note this, in the case of a system database failure, you need to apply the differential backup once and only once no matter when the failure is occurring during the week.
When is a database in complete recovered state?
A database is considered to be completely recovered when it is ready for use. At this point, you cannot restore any additional backups. When we say a database is completely in its recovered state then we automatically mean that no further backups can be applied since the chain of the database backup recovery and restoration is now broken by a new transactional recovery.
How to understand if the recovery process in ongoing?
When a database is in a state of ongoing recovery process you will see a “restoring” tag in the SQL Server – Object Explorer. The database is in a state where one backup file has been completely restored and the next backup in line is queued up for restoration. Any database that is undergoing a recovery process is incapable of accepting new information from the user end. This prevents any new modification of the data already stored.
This is why once a database restoration and recovery process is complete you cannot place it back into a recovery state to include a new restore process. To make sure that your database is in the middle of a restore process and can accept differential and transaction log backups, you will need to have a NO_RECOVERY keyword included in the WITH clause of the restore command.
What are transaction log backups?
The transaction log backups are the accelerants of any restore process. You can use these files to push a database forward to any time point that is covered in the time span of the particular transaction log backup.
This means, any transactions that are left uncommitted at the end of the backup are able to accept further additional backups to the database being restored. Transaction log backups have all the information required to apply all transactions chronologically without any collision of timelines.
Sample this – if you choose to bring a database into RECOVERY it will roll back to uncommitted transactions. If you choose NO_RECOVERY, this command will leave the database uncommitted and intact.
Conclusion
Backups determine the destiny of databases and your restore options are the actionable points in the entire life of the database that determine the immediate outcomes. Restore options can overwrite any existing database or they can replicate a functional database without overwriting any information. Overwriting is especially recommended when you need to “replace” all the database information that is broken or corrupted. And a RESTORE WITH RECOVERY option brings the database back to life with all necessary backups restored in the right locations.
