We backed up our production database to restore our development database.
When we restored our development database from the backup it triggered the production database to restore as well.
The production database did not actually restore though...
I verified we applied the restore to the correct database by running
WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1
This showed that our dev database has been restored and production has not.
Why did SQL Server Management Studio say that the production database was being restored when it has not been?
It looks like someone else has had a similar experience https://sqlundercover.com/2017/08/14/ssms-source-database-restoring-after-performing-a-restore-to-a-new-destination/