Sql-server – SQL Server shows database in recovery

You can restore the database files to other locations using T-SQL commands to do the restore.

I did a test, something like:

CREATE DATABASE TestDB
ON (FILENAME='C:\test\TestDB.mdf', NAME='TestDB')
LOG ON (FILENAME='C:\test\TestDBLog.ldf', NAME='TestLog');
GO

BACKUP DATABASE TestDB TO DISK='C:\Test\TestDB.BAK';
GO

DROP DATABASE TestDB;
GO

RESTORE DATABASE TestDB FROM DISK='C:\Test\TestDB.BAK'
WITH MOVE 'TestDB' TO 'C:\SomeOtherFolder\TestDB.mdf'
    , MOVE 'TestLog' TO 'C:\SomeOtherFolder\TestLog.ldf'
    , RECOVERY; 

This creates a database in one folder, takes a backup of the database, drops the database, then restores it from backup into a new folder. You should be able to modify the RESTORE DATABASE command to suit your needs.

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.