Wednesday, 7 March 2018

How to Recover SQL Server Database Manually?

The process of recovering the database is very important, which happens sometimes when the database is unexpectedly or incorrectly closed. When this happens, the database remains in an inconsistent state because, as we have already mentioned, the SQL Server databases work with information in memory, that is, when data modifications are made in the tables, these must be carried beforehand. pages of data involved in the memory, once in memory the modifications are made, but what happens if just after the change, the flow of electrical energy to the server is cut, as we know the pages were worked in memory, and this resource is volatile, then when the server is turned on again the pages in memory will have disappeared, now how is it that SQL Server ensures the durability of the information when transactions are made ?, well it does so through the transaction log .

Recall the process a bit, when the pages are already in memory and the respective changes are made in terms of data, SQL Server writes in the transaction log the changes made by the transaction, this writing if it is on disk, that is, the pages are modified in memory, but the transaction log is modified on disk, once the writing in the transaction log has been made, the database only informs the user that the transaction has been confirmed. Then when the server is turned on after the database has been closed incorrectly, SQL Server runs a recovery process for each of the databases, which first makes a "Rollforward" and then a "Rollback" , after this the information remains in a consistent state.

A "Rollforward" is a process by which SQL Server reads the transaction log and begins to apply each and every one of the changes made to the pages of the database after the last checkpoint. At this point it does not matter if the transaction has been confirmed or rejected, simply apply all the changes.

Then in the "Rollback" process, SQL Server begins to rollback all transactions that were not confirmed in addition to those that were rejected, thus leaving the database in a consistent state.

This recovery process in some cases may take a long time due to the large amount of information that they have to replicate from the transaction log. That is why the frequency with which checkpoints are made within the database is crucial for the time it takes for the server to execute the recovery process.

Additionally it is worth mentioning that in a few occasions the termination of the SQL Server service unexpectedly can cause corruptions of data, and this is serious because in some cases the information may be recoverable, but always with a risk of losing some of the information. data, and in others it is not possible to fix the database, then the only thing that remains in these situations is the restoration of backups and that is where if you have a good backup strategy you can get to recover all the information until the moment of the disaster.

Well this has been a brief explanation in what refers to the recovery process that runs on the database server every time the service shuts down in an incorrect way.

0 comments:

Post a Comment