This is a fantastic feature in databases but you will not realize how valuable this until you come across this situation. Before describing these features, 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 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 the 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 a designer, to modify database schema. Always use scripting for schema design changes.
· Always, use the Full recovery model for production.
Reference:
https://www.sqlshack.com/point-in-time-recovery-with-sql-server/
PN: Customer identity is hidden and will not be disclosed as per the policy. However, tried to expose technical approaches and tasks.
No comments:
Post a Comment