如何用C#结合SQL脚本实现SQL Server数据库备份并监控进度？

Hey there! Let's get your C# code sorted for running that SQL Server backup with progress monitoring. First, let's fix and complete your existing code, plus add the progress tracking logic that works with your existing monitoring script.

First, a critical note: You can't run a BACKUP DATABASE command inside a transaction in SQL Server. So we'll remove that BeginTransaction() line from your code—it'll cause an error otherwise.

Here's the complete, async-ready code that executes your backup stored procedure and monitors progress:

// Mark the event handler as async since we're using await
protected async void Backup_click(object sender, EventArgs e)
{
    var backupLabel = "DB_TEST_1_18_PostScript";
    // Use parameterized query to avoid SQL injection (always better practice!)
    var backupQuery = "EXEC [Test].[dbo].[Usp_Test_Backup_Database] @BackupLabel = @Label";

    using (var connection = new SqlConnection(ConnectionString))
    {
        await connection.OpenAsync();

        // Run the backup in a background task so we can monitor progress simultaneously
        var backupTask = Task.Run(async () =>
        {
            using (var backupCommand = new SqlCommand(backupQuery, connection))
            {
                backupCommand.Parameters.AddWithValue("@Label", backupLabel);
                // Execute the backup asynchronously
                await backupCommand.ExecuteNonQueryAsync();
            }
        });

        // Monitor progress while the backup runs
        while (!backupTask.IsCompleted && !backupTask.IsFaulted)
        {
            // Call your existing progress monitoring stored procedure
            using (var progressCommand = new SqlCommand("EXEC [Test].[dbo].[Usp_Get_Backup_Progress]", connection))
            {
                var progressResult = await progressCommand.ExecuteScalarAsync();
                
                if (progressResult != DBNull.Value)
                {
                    double progressPercent = Convert.ToDouble(progressResult);
                    // Update your UI here (adjust based on your app type: WinForms/WPF/ASP.NET)
                    // For example, in ASP.NET you might use SignalR to push updates to the client
                    // Or in WinForms: progressBar1.Value = (int)progressPercent;
                    Console.WriteLine($"Current Backup Progress: {progressPercent:F1}%");
                }
            }

            // Wait 1 second before checking progress again to avoid overwhelming the server
            await Task.Delay(1000);
        }

        // Handle the final result of the backup
        try
        {
            await backupTask;
            // Backup succeeded! Notify the user
            Console.WriteLine("Database backup completed successfully!");
        }
        catch (Exception ex)
        {
            // Handle backup failure
            Console.WriteLine($"Backup failed with error: {ex.Message}");
        }
    }
}

Key Details to Note: #

• Async/Await: We use async operations to keep your app responsive (no frozen UI if this is a desktop app, no blocked requests if it's web).
• Parameterized Query: Always use parameters instead of hardcoding values to prevent SQL injection.
• Background Backup Task: Task.Run offloads the backup to a background thread so we can monitor progress without blocking.
• Progress Polling: We check the progress every second—adjust the Task.Delay value if you want more/less frequent updates.

For Your SQL Monitoring Script #

If your existing Usp_Get_Backup_Progress needs a tweak, here's a sample implementation that queries SQL Server's system views to get backup progress:

CREATE PROCEDURE [Test].[dbo].[Usp_Get_Backup_Progress]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        COALESCE(percent_complete, 0) AS BackupProgress
    FROM
        sys.dm_exec_requests req
    JOIN
        sys.databases db ON req.database_id = db.database_id
    WHERE
        req.command LIKE 'BACKUP DATABASE%'
        AND db.name = 'Test'; -- Replace with your target database name
END

Make sure your backup stored procedure includes WITH STATS = 10 (or another percentage) in the BACKUP command—this tells SQL Server to report progress updates, which the system view picks up.

Additional Tips #

• Permissions: The account running your C# code needs BACKUP DATABASE permissions on the target SQL Server database, plus access to query sys.dm_exec_requests.
• ASP.NET Considerations: If this is for an ASP.NET app, you'll need to use something like SignalR to push progress updates to the client—you can't just update a label directly since HTTP is stateless.
• Error Handling: Add more specific exception handling (e.g., SqlException for SQL-specific errors) if you need to handle different failure scenarios.

内容的提问来源于stack exchange，提问作者Abraham Dhanyaraju Arumbaka