SQL Server:数据库处于“恢复中”状态

我备份了一个数据库:

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

现在数据库处于还原状态。

有些人推测，这是因为备份中没有日志文件，需要使用以下方法前滚:

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.

在灾难性的情况下，你想要的是一个无法工作的恢复。

备份包含数据文件和日志文件:

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

当前回答

你试过运行验证吗?确保这是声音备份。

http://msdn.microsoft.com/en-us/library/ms188902.aspx

2009-02-06 20:25:17

其他回答

当我在事件日志中也收到TCP错误时，我遇到了这个问题…

用sql删除数据库或在管理器“delete”中右键单击它然后再恢复。

实际上我已经开始默认这样做了。脚本数据库删除，重新创建，然后恢复。

2010-03-13 18:40:44

所有基于WITH RECOVERY的选项都不适合我。

所做的是做完整的恢复从管理工作室。

USE [master]
RESTORE DATABASE Sales_SSD
FROM  DISK = N'D:\databaseBackups02\Daily_Sales_20150309_0941.bak' 
WITH  FILE = 1,  
MOVE N'Sales_Data' TO N'C:\Data\SSD\Sales.mdf',  
MOVE N'Sales_Log' TO N'C:\Data\SSD\Sales_1.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

2015-03-26 09:31:01

如果您想从备份文件恢复SQL Server数据库，可以使用以下脚本:

RESTORE DATABASE [MyDatabase] -- which database to restore
FROM DISK = N'X:\MyDatabase.bak' -- location of the database backup
WITH 
    FILE = 1, -- restore from a backup file
    -- declare where the file groups should be located (can be more than two)
    MOVE N'MyDatabase_Data' TO N'D:\SSDPATH\MyDatabase.mdf',
    MOVE N'MyDatabase_Log' TO N'E:\HDDPATH\MyDatabase.ldf',
    -- Tape option; only relevant if you backup from magnetic tape
    NOUNLOAD,
    -- brings the database online after the database got restored
    -- use this option when you don't want to restore incremental backups
    -- use NORECOVERY when you want to restore differential and incremental backup files
    RECOVERY,
    -- replace existing database with the backup 
    -- deletes the existing database
    REPLACE, 
    -- print log message for every 1 percent of restore
    STATS = 1;

2020-11-19 09:04:17

好吧，我有类似的问题，就像在Pauk的情况下一样，这是由服务器在恢复时耗尽磁盘空间引起的，因此导致了永久恢复状态。如何在不停止SQL Server服务的情况下结束此状态?

我找到了一个解决方案:)

Drop database *dbname*

2009-06-26 10:11:54

为我解决问题的是

停止实例在data文件夹中创建.mdf和.ldf文件的备份重新启动实例删除恢复卡住的数据库把。mdf和。LDF文件返回到数据文件夹将实例附加到.mdf和.ldf文件

2018-10-31 21:36:14

SQL Server:数据库处于“恢复中”状态

推荐文章

最新文章

标签