How to fix a restoring database without the backup file in SQL Server?

Question

One of my colleagues was restoring a backup file on a database and then realized that it's not the right one, so he cancelled the restoring. Now the database is stuck in restoring state and there's nothing to do with it. How can we repair the database?

P.S 1: The last backup file was for several hours ago, so we don't want to restore it.

P.S 2: We have the .mdf and .ldf files; however, when we want to detach the database, in order to attach those files, we get the following error:

Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

Answer 1

I solved the problem with ApexSQL Log.

This link might help to anyone who gets this problem in the future:

https://solutioncenter.apexsql.com/recover-sql-server-database-using-only-a-transaction-log-file-ldf-and-old-backup-files/

Answer 2

I agree with Cdrrrrr solution. Drop the database and try to fix by third party software mentioned in this article.

Answer 3

Better to create a new Database with mdf file that you have and then drop your existing DB which in error state.

Answer 4

Sorry to be the bearer of bad news but you can't "fix" the database to revert to the original version once the restore starts. Your only recourse is to restore from you last full backup, restore the latest differential (if applicable), followed by transaction log backups (FULL or BULK_LOGGED recovery model).

Data will be recovered to point will be that of the last log backup and changes made after that will be lost.