Problem. After a failed setting mirror operation (initiated from SQL Server Management Studio from GUI), SQL Server Database stay in "in recovery" status for prolonged period of time (overnight). The database is about 7GB in size. From the primary site, the database showed normal. From the secondary site, the database showed "in recovery".
There is little that can be done for the secondary database. One option is to stop the SQL services, rename the data files (.mdf & .ldf), start the SQL database again, drop the existing database, rename the data files back, and attached the data files with same database name. After that, we can try to restore database backup and set up mirror.
On a closer look from primary, it seems that the mirror is broken. However, from the output of some database operation in the secondary database, it seems that the mirror has not be broken yet. Further more, there is no entry in the event log to indicate the progress of the recovery operation.
Let's try this approach. From the primary site, to break the mirror run the following command.
ALTER DATABASE TfsVersionControl SET PARTNER OFF
After the command finished successfully, the database on the secondary site changed to (Restoring…) status immediately. The following operations are straight forward -- copy the full database backup and log, and restore with norecovery option, set mirror for the database.
This is the preferred approach, since only the problem db is touched, no need to re-set the mirror for all other databases.
There are occasional complains from the internet about this prolonged (or hanged) SQL Server database "in recovery" status. It may worth to take a look whether the database actually started the "recovery", or still in a previous status due to unknown reasons.