This is the Guest post by my friend and SQL Server DBA Andrew Jackson
SQL Server being a reliable database management system has become the choice of millions of the users worldwide. However, the technical issues can arise anytime in such a heavy server which may lead to server breakdown or database corruption at any moment. The users who depend much on the database cannot afford to lose data, hence they prefer to create a backup of the data at regular interval of time. The database files can be easily recovered on the system in such fatal situations. The in-built recovery utility generally assures a smooth recovery of the database without introducing any data loss. However, some times users face an annoying situation in which the recovery process gets stuck up in the restoring phase for a long time. Keeping in mind the inconvenience faced by the users, we have focused to provide the manual solution for recovering a database that is in the restoring state reference for much time and is not responding to the recovery process.
How SQL Database Stuck in Restoring State ?
Generally, the SQL users attempt to restore the backup files using the SQL query. The database gets easily restored on the system within few minutes depending upon the size of the backup file.
Consider, we need to restore MyData database from MyData.bak backup file. The following SQL query can be run on SQL command prompt to restore the database:
RESTORE DATABASE MyData FROM DISK = 'MyData.bak' WITH REPLACE
Generally, the database gets restored on the system within few minutes. However, the REPLACE option used within the query leads to forced restore of the given database.
Moreover, it overwrites the existing database with the newly recovered database files. But, in some situations, the SQL Server database recovery progress is too slow that the restore process goes into the stuck mode for an indefinite time. Even if the user press the Stop button to cancel the restore process, he gets annoyed with SQL Server database restoring forever situation. So, in this situation neither the database can be conveniently restored nor the user knows how much estimated time would it take in recovering a database that is in the restoring state reference. This creates an annoyance for SQL users as they become unable to restore the crucial database, even after having the proper backup of the entire database.
How to Recover Database From Restoring State Reference?
Only a small trick can help the users in recovering a database that is in the restoring state reference for any point of time. The following SQL query can help the users to easily restore the database from the backup file without facing any stuck up situation. This script replaces the existing database and restores the database from the specified backup file.
RESTORE DATABASE MyData FROM DISK = 'MyData.bak' WITH REPLACE, RECOVERY
However, if the user does not want to replace the existing database then the Replace option should be eliminated from the query. The following SQL query must be run in order to backup the database without overwriting the existing database:
RESTORE DATABASE MyData FROM DISK = 'MyData.bak' WITH RECOVERY
Running the SQL query in this manner will not let the database to stuck up in the restore process and will allow a smooth recovery process.
NOTE: Users can choose to view the progress status of the recovery process. This feature can be adopted in order to have a continuous eye on the ongoing database recovery. If any unexpected time lag is noticed, then an immediate error detection can be performed to carry out the recovery of SQL database smoothly.
RESTORE DATABASE MyData FROM DISK = 'MyData.bak' WITH RECOVERY, Stats=1
The query just needs to add an additional parameter for viewing the status of recovery time to time.
The database backup is a very crucial task and needs to be carried out attentively by the user. Users must keep it a point to always add the transaction log files for the specified database backup. If the log files are missing, then SQL Server may keep on waiting for the missing log files and turns into stuck up of restore process. We can observe that a slight change in the query can figure out much changes in the SQL processing. We hope that the blog has provided an efficient manual solution that would help in recovering a database that is in the restoring state reference.