SQL Server:数据库卡在“还原"目录中状态 [英] SQL Server: Database stuck in "Restoring" state

问题描述

我备份了一个数据库:

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

然后尝试还原它:

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 

当然会失败:

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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf
MyDatabase_log  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF

推荐答案

您需要在数据库RESTORE命令中使用WITH RECOVERY选项，以使数据库联机，这是还原过程的一部分.

You need to use the WITH RECOVERY option, with your database RESTORE command, to bring your database online as part of the restore process.

仅当您不打算还原任何事务日志备份(即，您只希望还原数据库备份然后能够访问数据库)时，这才是当然.

This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

您的命令应如下所示，

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE,RECOVERY

使用SQL Server Management Studio中的还原数据库向导，您可能会有更多的成功.这样，您可以选择特定的文件位置，覆盖选项和WITH Recovery选项.

You may have more sucess using the restore database wizard in SQL Server Management Studio. This way you can select the specific file locations, the overwrite option, and the WITH Recovery option.

这篇关于SQL Server:数据库卡在“还原"目录中状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！