0

We've had an issue crop up a few times -- maybe we're not using 'best practices' to restore, but appreciate any advice. We run an IIS app with a SQL Server database, and sometimes we run two+ instances (multi-site) on the same server / SQL Server instance.

Let's say we have a database 'site1', and we create a site1.bak of that site. Then we need to set up another, so we restore site1.bak to a new database site2.

However, on restoring that database (and ensuring the file names are different), the ORIGINAL site says it's IN RECOVERY. The 'fix' is easy -- we run:

RESTORE DATABASE NameofDatabase
WITH RECOVERY

...but the fear is something else is going on that could be detrimental. Why would the original database go into restore when the .bak is being restored to a new database with different file names?

migrated from stackoverflow.com Feb 29 '16 at 17:49

This question came from our site for professional and enthusiast programmers.

  • 2
    Offtopic. Not a programming question. Try the DBA site. – Marc B Feb 29 '16 at 16:46
3

on restoring that database (and ensuring the file names are different), the ORIGINAL site says it's IN RECOVERY. The 'fix' is easy ...

You are restoring on the same database and using norecovery in your script. You should use (pseudo code) ..

restore database dbname
from disk = 'path to your backup'
with recovery, stats =10,
move 'logicalName' to 'physical_path\new_dbname.mdf',
move 'logicalLogFileName' to 'physical_path\new_dbname_log.ldf'
2

"You are restoring on the same database and using 'norecovery' in your script"

Didn't use a script to restore -- right clicked on Databases and clicked restore, then used the .bak from the original site. Perhaps when one does that it's using norecovery?

When restoring the DB, we're naming a new DB name and making sure the file names are different. There are two databases in the instance when the restore is complete, but the ORIGINAL goes into recovery. The second is there and available.

Your Answer

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