Sql-server – SQL Server database stuck in Recovery after Restoring .BAK to same instance

instancerecoveryrestoresql server

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?

Best Answer

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'

Related Solutions

SQL Server 2005 – How to Move (Restore) a Database That Does Not Exist

No, for a restore you don't have to have the database existing already (be mindful though to not have the with replace option set in your restore database command).

The below statement would create a new database from the backup:

RESTORE DATABASE [YourNewDbName] 
FROM  DISK = N'C:\YourBackupFile.bak' 
WITH  FILE = 1,  
    MOVE N'YourDataFile' TO N'C:\YourNewDbName.mdf',  
    MOVE N'YourLogFile' TO N'C:\YourNewDbName_1.ldf',  
    NOUNLOAD,  
    STATS = 10
GO

Obviously, modify the parameters accordingly. But this doesn't require YourNewDbName to exist prior to the restore database.

SQL Server – Troubleshooting Database Recovery Stuck on ‘Restoring’

Yeah, you need that .ldf file. In the future, don't delete that.

If your database is running in full recovery, you need to run log backups regularly. "Regularly" is a term that depends on your particular environment, but hourly during business hours is a common choice. Basically, it only purges the transactions out of the log file when it's backed up. If it's never backed up, the log grows until it consumes the entire disk, as you've seen.

In the future, set the database to simple recovery, shrink the transaction log, and then back it up immediately, as NathanC suggested.

As for now: I don't know what kind of backups you have beyond the one you restored from, but I agree with NathanC that you need to let it sit and wait for the restore to finish. It's chugging through that log file. Once it finishes, truncate the log and set it to simple like your other databases, or implement log backups.

Best Answer

Related Solutions

SQL Server 2005 – How to Move (Restore) a Database That Does Not Exist

SQL Server – Troubleshooting Database Recovery Stuck on ‘Restoring’

Related Question