Sql-server – Restoring development database from production backup triggered production database to restore as well

backuprestoresql serversql-server-2016ssms

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/

Best Answer

Found the answer here https://sqlundercover.com/2017/08/14/ssms-source-database-restoring-after-performing-a-restore-to-a-new-destination/

When configuring the restore there is an option to keep the source database in a restore mode while copying the tail log. This is selected by default.

Related Question