Restoring a backup of 1 database to another puts both databases into Restoring mode

Question

We have a 2 databases-

• Staging
• Test

At times, we refresh out Test environment with a copy of Staging. We do this by taking a full backup of the Staging database and restoring it over the Test database.

When we do this, both databases go into Restoring... mode and only the Test databases comes back online after the restore.. the Staging database shows Restoring... until we issue a RESTORE DATABASE Staging WITH RECOVERY.

Why is this and what do we need to change about our process to prevent this happening? The backup and restore event log on the Staging database doesn't show any evidence of a restore on the database.

I'm thinking it's something to do with Logical File Names. When we do the restore, we change the .dbf and .ldf files to point to the TEST files, however the logical files are still pointing to STAGING, something we can't change through SSMS during a restore.

The restore command that SSMS is running is:

USE [master]
BACKUP LOG [Chris21_STAGING] TO DISK = N'F:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Chris21_STAGING_LogBackup_2016年06月24日_11-12-03.bak' WITH NOFORMAT, NOINIT, NAME = N'Chris21_STAGING_LogBackup_2016年06月24日_11-12-03', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
ALTER DATABASE [Chris21_TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Chris21_TEST] FROM DISK = N'C:\Chris21_STAGING Backup 20160624.bak' WITH FILE = 1, MOVE N'Chris21_STAGING' TO N'F:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chris21_TEST.mdf', MOVE N'Chris21_STAGING_log' TO N'F:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chris21_TEST_log.ldf', NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [Chris21_TEST] SET MULTI_USER

Answer 1

It seems to me that in UI you have 'Backup tail of the log, and leave database in restoring state' selected which is causing the issue. - https://dba.stackexchange.com/users/46744