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
