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
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
  • Can you give us some clue about the actual RESTORE commands you're issuing? I can't sort out the word problem but clearly there is something wrong with the commands. Restoring a database can't put another database into restoring mode... – Aaron Bertrand Jun 24 '16 at 1:11
  • Just added it. It does show something interesting- the Tail Log backup is coming from the Staging database?? And it is putting into NORECOVERY so that would explain it. – Lock Jun 24 '16 at 1:15
  • This is one of about 87 reasons I don't use the UI for any backup/restore operations. – Aaron Bertrand Jun 24 '16 at 1:39
  • Yep however the front end should be able to handle this and we don't have issues on any other databases so there must be something specific to this one that is making it do something different. – Lock Jun 24 '16 at 1:52
  • 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. – SQLPRODDBA Jun 24 '16 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.