sql-server - SQL Server：数据库陷入“恢复”状态(SQL Server: Database stuck in “Restoring” state)

Question

I backed up a database:

(我备份了一个数据库：)

BACKUP DATABASE MyDatabase
TO DISK = 'MyDatabase.bak'
WITH INIT --overwrite existing

And then tried to restore it:

(然后尝试恢复它：)

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE --force restore over specified database

And now the database is stuck in the restoring state.

(现在数据库仍处于恢复状态。)

Some people have theorized that it's because there was no log file in the backup, and it needed to be rolled forward using:

(有些人认为这是因为备份中没有日志文件，需要使用以下方式前滚：)

RESTORE DATABASE MyDatabase
WITH RECOVERY 

Except that, of course, fails:

(当然，除此之外，失败：)

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

And exactly what you want in a catastrophic situation is a restore that won't work.

(确切地说，在灾难性的情况下你想要的是一种无法恢复的恢复。)

---

The backup contains both a data and log file:

(备份包含数据和日志文件：)

RESTORE FILELISTONLY 
FROM DISK = 'MyDatabase.bak'

Logical Name    PhysicalName
=============   ===============
MyDatabase    C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMyDatabase.mdf
MyDatabase_log  C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMyDatabase_log.LDF

  ask by Ian Boyd translate from so

Answer 1

I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d.

(我有这种情况使用Symantec Backup Exec 11d将数据库还原到SQL Server 2005 Standard Edition实例。)

After the restore job completed the database remained in a "Restoring" state.

(还原作业完成后，数据库仍处于“正在恢复”状态。)

I had no disk space issues-- the database simply didn't come out of the "Restoring" state.

(我没有磁盘空间问题 - 数据库根本没有出现“恢复”状态。)

I ran the following query against the SQL Server instance and found that the database immediately became usable:

(我针对SQL Server实例运行了以下查询，发现数据库立即可用：)

RESTORE DATABASE <database name> WITH RECOVERY