如何通过编码实现每月执行SQL备份代码（替代SSMS作业/维护计划）

First, let's fix your existing ASP.NET button click handler to perform the actual database backup, then we'll cover how to automate this task to run monthly.

Step 1: Correct the Backup Logic in Your Code-Behind #

Your current code starts with a SqlConnection, but SQL backups are executed via a T-SQL BACKUP DATABASE command. Here's the revised code for your .aspx.cs file:

using System.Configuration;
using System.Data.SqlClient;
using System;

protected void Button1_Click(object sender, EventArgs e)
{
    // Fetch your connection string (ensure it has backup permissions)
    string connectionString = ConfigurationManager.ConnectionStrings["myc"].ConnectionString;
    // Define backup path (use a directory SQL Server's service account can write to)
    string backupPath = @"C:\SQLBackups\MyDatabase_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".bak";

    try
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            string backupCommand = $"BACKUP DATABASE [YourDatabaseName] TO DISK = '{backupPath}' WITH INIT, COMPRESSION;";
            
            con.Open();
            using (SqlCommand cmd = new SqlCommand(backupCommand, con))
            {
                cmd.ExecuteNonQuery();
            }
            
            // Optional: Notify user of success
            Response.Write("Backup completed successfully!");
        }
    }
    catch (Exception ex)
    {
        // Handle errors (log to file, show user-friendly message)
        Response.Write($"Backup failed: {ex.Message}");
    }
}

Important Notes: #

• Replace YourDatabaseName with your actual database name.
• The SQL Server service account needs write permissions to the backupPath directory (use a network share if needed, and grant access there too).
• WITH INIT overwrites existing backups at the path; use WITH NOINIT to append to an existing backup set.
• COMPRESSION reduces backup size (available in SQL Server Standard/Enterprise editions).

Step 2: Automate Monthly Execution #

An ASP.NET button requires manual clicks, so we need a way to run this code automatically every month. Here are the most reliable options:

Option 1: Windows Task Scheduler + Console App (Most Reliable) #

This method keeps the task independent of your web app, avoiding issues with app pool recycling:

1. Create a Console App:

   • Make a new .NET Console Application project.
   • Add the backup logic (adjusted for console output):

     using System;
     using System.Configuration;
     using System.Data.SqlClient;
     
     class Program
     {
         static void Main(string[] args)
         {
             string connectionString = ConfigurationManager.ConnectionStrings["myc"].ConnectionString;
             string backupPath = @"C:\SQLBackups\MyDatabase_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".bak";
     
             try
             {
                 using (SqlConnection con = new SqlConnection(connectionString))
                 {
                     string backupCommand = $"BACKUP DATABASE [YourDatabaseName] TO DISK = '{backupPath}' WITH INIT, COMPRESSION;";
                     con.Open();
                     using (SqlCommand cmd = new SqlCommand(backupCommand, con))
                     {
                         cmd.ExecuteNonQuery();
                     }
                     Console.WriteLine("Backup completed successfully!");
                 }
             }
             catch (Exception ex)
             {
                 Console.WriteLine($"Backup failed: {ex.Message}");
             }
         }
     }
     

   • Add your connection string to the console app's App.config file.

2. Schedule with Windows Task Scheduler:

   • Open Task Scheduler → Create Basic Task.
   • Name it (e.g., "Monthly Database Backup") and set the trigger to Monthly, picking your preferred day/time.
   • Set the action to Start a program, then select your compiled console app .exe.
   • Run the task under an account with permissions to execute the app and write to the backup directory.

Option 2: Hangfire (For ASP.NET Web Apps) #

If you want to keep the logic within your web app, Hangfire handles background/scheduled tasks:

1. Install Hangfire:

   • Use NuGet to install Hangfire and Hangfire.SqlServer.

2. Configure Hangfire:

   • In Global.asax (ASP.NET Framework) or Startup.cs (ASP.NET Core), set up Hangfire with your connection string.

3. Schedule the Monthly Backup:

   // For ASP.NET Framework (Global.asax)
   protected void Application_Start()
   {
       // Initialize Hangfire
       GlobalConfiguration.Configuration.UseSqlServerStorage(ConfigurationManager.ConnectionStrings["myc"].ConnectionString);
   
       // Schedule backup on the 1st day of every month at 2 AM (cron expression: minute, hour, day, month, weekday)
       RecurringJob.AddOrUpdate(
           "MonthlyDatabaseBackup",
           () => PerformBackup(),
           "0 2 1 * *"
       );
   }
   
   // Reusable backup method
   public void PerformBackup()
   {
       // Same backup logic as Step 1 (log to a file instead of using Response.Write)
   }
   

Caveats for Web App Scheduling: #

• Web apps can be shut down by the server (app pool recycling), which may interrupt tasks. Hangfire requeues tasks, but it's less reliable than a dedicated console app + Task Scheduler.
• Ensure your app stays running (use Azure Web Jobs if hosted in Azure).

Key Permissions Reminder #

Whichever method you choose:

• The account running the code needs BACKUP DATABASE permission on the target SQL Server database.
• The account must have write access to the backup directory.

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