☰

为什么 2 小时后 MSSQL 仍停留在“正在恢复... ”状态？

tag-icon • tag-icon windows-server-2003 sql-server sql-server-2005
为什么 2 小时后 MSSQL 仍停留在“正在恢复... ”状态？

我已经在旧服务器上备份了数据库，并在新服务器上恢复了它。我对 MSSQL 2005 Server Express 完全陌生，所以花了很长时间。我花了 30 分钟才发现备份存储在服务器上，而不是 MSSQL 2005 Express Studio 桌面上。

成功恢复后，2 小时后 Express Studio 中仍显示“正在恢复...”。数据库大小只有 4.8 MB，所以我想现在应该早就完成了。

我运行了“无恢复”的恢复命令。Sankar 给出的 SQL 命令给出以下输出：

TEID-UGNCHQ\SQLEXP_VIM;NetPerfMon;BACKUP DATABASE [NetPerfMon] TO  DISK = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\NetPerfMon.bak' WITH  RETAINDAYS = 1, NOFORMAT, INIT,  NAME = N'NetPerfMon-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;2011-05-25 18:16:04.527;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10;2011-05-25 18:47:32.843;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10;2011-05-25 18:47:53.453;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10;2011-05-25 18:48:14.627;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10;2011-05-25 19:01:19.970;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  MOVE N'VeeamBackup' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\VeeamBackup.mdf',  MOVE N'VeeamBackup_log' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\VeeamBackup_log.LDF',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10;2011-05-25 19:09:58.370;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator

答案1

当您恢复数据库时，您是否选中了 NORECOVERY 复选框（或使用了 NORECOVERY 关键字）？

答案2

如果您不确定如何运行之前使用的 RESTORE 命令，那么您可以使用以下查询通过默认跟踪来查找。HTH。

DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\',REVERSE(path))+1) + '\Log.trc' 
FROM sys.traces  
WHERE is_default = 1; 

--Check all the databases that are backed up and restored and their success/failure state.
SELECT
     gt.ServerName
     , gt.DatabaseName
     , gt.TextData
     , gt.StartTime
     , gt.Success
     , gt.HostName
     , gt.NTUserName
    , gt.NTDomainName
     , gt.ApplicationName
     , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
LEFT OUTER JOIN sys.databases d ON gt.DatabaseName = d.name
WHERE EventClass = 115 --'Audit Backup/Restore Event'
ORDER BY StartTime;

相关内容