Recently we encountered the below issue while restoring a database. And we also observed the database has gone into “restoring” mode.
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database “dbname” as read from the backup set.
The message is clear that there is something wrong on the backup set. There could be many issues related, but most likely reason would be the backup file would have got file corrupted.
You would likely to take a fresh backup and try with restore and in most cases it works good. So, the take away is “we should always validate our backups”. This is exactly what Paul S Randal explains with a survey and more details as here.
How to recover restoring database in SQL Server
How to recover “Recover Pending” database in SQL Server
If you enjoyed this blog post, feel free to share it with your friends!