SQL Server完整备份与差异备份恢复报错求助

报错信息 #

"The log or differential backup cannot be restored because no files are ready to rollforward.\r\nRESTORE DATABASE is terminating abnormally.\r\nChanged database context to 'master'."

相关代码 #

生成恢复脚本的WriteFile()方法 #

private void WriteFile()
{
    try
    {
        // Creates Restore.sql file.
        string strTSQLFile = Environment.CurrentDirectory + "\\Restore.sql";
        FileInfo File = new FileInfo(strTSQLFile);
        StreamWriter Writer = File.CreateText();

        // Write open database  
        string strTemp;
        Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        Writer.WriteLine("Go");
        if (Complete_Opt.Checked == true)           // Full restore
        {
            strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH RECOVERY";
            Writer.WriteLine(strTemp);
        }
        else if (Differential_Opt.Checked == true)  // Quick restore
        {
            strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH RECOVERY";
            Writer.WriteLine(strTemp);
            Writer.WriteLine("GO");
            //Writer.WriteLine("WAITFOR DELAY '00:00:10'");
            //Writer.WriteLine("GO");
            Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET MULTI_USER");
            Writer.WriteLine("GO");
            Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
            Writer.WriteLine("Go");
            strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + txt_Quick.Text + "\' WITH NORECOVERY";
            Writer.WriteLine(strTemp);
        }

        Writer.WriteLine("GO");
        Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET MULTI_USER");
        Writer.WriteLine("GO");
        Writer.Close();
    }
    catch (Exception Exc)
    {
        throw Exc;
    }
}

SQL执行逻辑 #

try
{
    string strTSQLFile = Environment.CurrentDirectory + "\\Restore.sql";
    if (!File.Exists(strTSQLFile))
    {
        throw new FileNotFoundException();
    }
    using (StreamReader srSQL = new StreamReader(strTSQLFile))
    {
        string sqlLine;
        StringBuilder sqlString = new StringBuilder();

        while (!srSQL.EndOfStream)
        {
            sqlLine = srSQL.ReadLine();

            if (string.IsNullOrEmpty(sqlLine) == false)
            {
                // We don't actually execute the "GO" lines but can use them to determine when to call the executenonquery function
                if (string.Compare(sqlLine, "GO", true) == 0)
                {
                    // Make sure we have something to execute
                    if (string.IsNullOrEmpty(sqlString.ToString()) == false)
                    {
                        ServerActionResult.ConnectionContext.ExecuteNonQuery(sqlString.ToString());
                        System.Diagnostics.Debug.WriteLine(sqlString);
                    }

                    sqlString.Clear();
                }
                // Add the next line to the stringbuilder object
                else
                {
                    sqlString.AppendLine(sqlLine);
                }
            }
        }
    }
}
catch (Exception exc)
{
    throw exc;
}
finally
{
    var strQuery = ("ALTER DATABASE [" + DB_NAME + "] SET MULTI_USER");
    ServerActionResult.ConnectionContext.ExecuteNonQuery(strQuery);
}

调试输出信息 #

ALTER DATABASE [PharmSpecDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE MASTER RESTORE DATABASE [PharmSpecDB] FROM DISK = 'C:\PharmBackup\Backup\PharmSpecDB_14-Nov-2024-13-38-12-520_Full.Fbk' WITH RECOVERY

ALTER DATABASE [PharmSpecDB] SET MULTI_USER

ALTER DATABASE [PharmSpecDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Exception thrown: 'Microsoft.SqlServer.Management.Common.ExecutionFailureException' in Microsoft.SqlServer.ConnectionInfo.dll

备份创建代码 #

Backup bk = new Backup();

string szFilename = DB_NAME + "_" + DateTime.Now.ToString("dd-MMM-yyyy-HH-mm-ss-fff", CultureInfo.InvariantCulture);

//Setup the backup options
if (enumBackupMode == BACKUP_MODE_ENUM.BACKUP_FULL)
{
    szFilename += "_Full.Fbk";
    bk.BackupSetDescription = "PharmSpecDB Full Backup";
    bk.BackupSetName = DB_NAME + " Backup";
    bk.LogTruncation = BackupTruncateLogType.Truncate;
    bk.Incremental = false;
}
else if (enumBackupMode == BACKUP_MODE_ENUM.BACKUP_QUICK)
{
    szFilename += "_Quick.qbk";
    bk.BackupSetDescription = "PharmSpecDB Quick Backup";
    bk.BackupSetName = DB_NAME + " Backup";
    bk.LogTruncation = BackupTruncateLogType.Truncate;
    bk.Incremental = true;
}
bdi = new BackupDeviceItem(szLocation + szFilename, DeviceType.File);
bk.Devices.Add(bdi);
bk.Action = BackupActionType.Database;
bk.Database = DB_NAME;
bk.RetainDays = 365;

//Perform the backup
bk.SqlBackup(ServerActionResult);

原因分析 #

1. 完整备份恢复使用WITH RECOVERY破坏恢复链：差异恢复流程中，完整备份恢复后执行WITH RECOVERY会让数据库进入可用状态，截断事务日志，后续差异备份无法继续回滚（数据库已完成恢复，无法接收增量备份）。
2. 多余的数据库状态切换：完整备份恢复后切换多用户再切回单用户，完全没必要，还会打断恢复链的连续性，增加出错风险。

解决方案 #

修改WriteFile()方法中差异备份分支的逻辑，确保完整备份恢复时用WITH NORECOVERY保持数据库恢复状态，再恢复差异备份，最后统一完成恢复：

else if (Differential_Opt.Checked == true)  // Quick restore
{
    // 完整备份恢复用NORECOVERY，保持数据库可接收后续差异备份
    strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH NORECOVERY";
    Writer.WriteLine(strTemp);
    Writer.WriteLine("GO");
    // 恢复差异备份并完成恢复，让数据库进入可用状态
    strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + txt_Quick.Text + "\' WITH RECOVERY";
    Writer.WriteLine(strTemp);
}

核心说明 #

• NORECOVERY：用于完整备份恢复，让数据库处于“正在恢复”状态，允许后续恢复差异或日志备份。
• RECOVERY：用于最后一步（差异备份恢复），完成所有恢复操作后让数据库进入可用状态。
• 移除多余的ALTER DATABASE切换：恢复过程中保持数据库单用户状态，避免干扰恢复链。

内容的提问来源于stack exchange，提问作者nikhil