I've encountered this scenario twice so far and I still don't understand what's happening.
I'm doing a database migration following roughly these steps.
- Restore my FULL/DIFF/Logs WITH NORECOVERY on the new server
- Take tail of log backup on old server (usually requires a SET SINGLE_USER also)
- Restore tail of log on new server WITH RECOVERY
So far so good, the new DB is available and the old one is in "Recovering" state so no longer available. Exactly what I want.
Now for whatever reason there is an issue and we need to rollback to the old server. I'm expecting to simply need to run "RESTORE DATABASE MyDB WITH RECOVERY" but twice now running this command just completely froze the connection. I let it run for 15-30 minutes and absolutely nothing is happening, I see it in sp_whoisactive, it's not blocked by anything and I'm running it in the same session as the SET SINGLE_USER / Tail of Log. Both time I ended up restarting the SQL Server service and the databases come back online instantly, no issues. But I obviously don't want this to my go-to solution.
I should also mention that both time this happened was on SQL Server 2012, two different instance but both fully patched. We are not talking about huge databases either, both time I encountered the issue was with DBs <5GB.
Am I doing something wrong?
To clarify in case I wasn't clear, I'm basically doing the following code (but of course this worked without issue when I tested it so it's probably not that simple)
CREATE DATABASE TestRestore
BACKUP DATABASE TestRestore TO DISK = N'C:\Temp\TestRestore_FULL.BAK'
ALTER DATABASE TestRestore SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG TestRestore TO DISK = N'C:\Temp\TestRestore_Tail.trn' WITH NORECOVERY
RESTORE DATABASE TestRestore WITH RECOVERY /* Freeze here */