2

I'm trying to restore my development database DEV with a backup of my production database PROD. Every time I go through this process, the DEV database gets stuck in a Restoring state. Using the SQL "RESTORE DATABASE DEV WITH RECOVERY" does not break it out of the Restoring state. The only way I can get it out of that state is by using the Restore Database wizard. I have generated a script from that wizard and, with the exception of the SINGLE_USER and MULTI_USER lines, the SQL is the same as what I'm using below.

USE [master]

ALTER DATABASE DEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DEV
FROM DISK = N'D:\Backup\PROD-20160629.bak'
WITH FILE = 1,
MOVE N'PROD' TO N'D:\Data\DEV.mdf',
MOVE N'PROD_log' TO N'D:\Logs\DEV.ldf',
NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE DEV SET MULTI_USER

Why does my database get stuck in a Restoring state from this statement?

  • 2
    If you just want to restore the database from the bak file and not apply any transaction logs, use WITH RECOVERY. You could use that in place of NOUNLOAD - why do restore statements still have this thing from the 1990s? – Aaron Bertrand Jun 29 '16 at 16:50

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.