We have a 2 databases-
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
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
.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