Recover from a Data Disaster – Point in Time Recovery Method

If you are a database administrator, you will never know when your database hit a disaster. When the disaster hit your database, you will be in a panic mode and you will take action which you won't take in modest conditions. However, we need to plan for a disaster. This article provides you with the importance of Point in Time Recovery Method in order to recover data.

This is a fantastic feature in databases but you will not realize how valuable this until you come across this situation. Leaving describing these features to separate time, let us first look at two cases. Incidentally, these two incidents occurred in one organization and moreover for one database but at different times.

Incident #1:  A developer was connected to both production and development instances of databases from one SQL Server Management Studio (SSMS) instance. If you have work with SSMS, you would understand how risky this is. If not, this incident tells you how risky it is. The DBA thinking that she is working on the developer instance has deleted data in a customer table in Production.

Incident #2: In the same organization, they decided to increase the column length to 50 from 15. This column is a critical column to the business. They increased the size of the column using the design view from a table. Guess what, before typing the 0, after removing the 1 the user has saved the designed, which had resulted in the truncation of the table column to 5 from 15 with a data loss!!

Action: In both cases, we were to help. The first question was “What is the recovery model?” if it is Simple, then nothing that we could do as Simple recovery model does not keep the transaction log history and nothing can be recovered. In these scenarios, the recovery model is Full means that we can recover data to a point of time. In this mechanism, we can recover to a given time. So, we got the full back up of database and restored to a different database instance with the recovery option on. Then we got a log backup and restored on top of the previously restored database with specifying a time which is just before the disaster recovery. After the database is restored fully, customer table and the other tables were transferred to the production database.

Lesson Learnt:

• ·  Never connect to multiple databases instances from SSMS. Especially, with production and other database instances.
• ·  Never use the SSMS designer, to modify the database schema. Always use scripting for schema design changes. 
• ·  Always, use the Full recovery model for production.

• If you need more details, read the article written by me at sqlshack. Further, you can use Log shipping to avoid data disasters as discussed in the latest article.