0

I have developed an ASP.NET Core web application, I need to write a C# method to allow the end user to download or save the SQL Server database full backup on his local computer. Moreover, I need to upload that database backup to restore a database.

Here is an example C# method that I wrote to download and save only one table of data in Excel format. Now I want to write another method to allow the user to download and save the all table full-backup as a .bak file on his local computer.

Please guide how to achieve this, thank you.

[DisplayName("Export Data To Excel")]
[Authorize(policy: "HasAccess")]
public class ExportDataToExcelController : BaseController
{
    public ExportDataToExcelController(HoshmandDBContext context) : base(context)
    {
    }

    // ----Export Patient Data -----------------------------
    public IActionResult ExportPatientDataToExcel()
    {
        DataTable patientTable = new DataTable("Patients_Data");
        patientTable.Columns.AddRange(new DataColumn[9]
        {
              new DataColumn("Patient ID"),
              new DataColumn("First Name"),
        });

        var patientsList = _context.PatientTbs.Include(a => a.Doctor).ToList();

        foreach (var patient in patientsList)
        {
            patientTable.Rows.Add(patient.PatientId, patient.FirstName);
        }

        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(patientTable);

            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Patients Data " + GetLocalDateTime().Date.ToShortDateString() + "_" + ".xlsx");
            }
        }
    }
}

Here is the user interface screenshot that I can download only one table of data in Excel format:

enter image description here

Here is an example user interface for restoring the database backup:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hedayat Hoshmand
  • 39
  • 2
  • 12
  • You can write C# code to run a SQL script to handle the backup and restore - ***BUT*** that's going to be **on the (possibly remote) server** where your SQL Server is running. You cannot directly back up to a local drive. The only way to do this is to **(1)** share a local drive as a UNC path and allow the server's SQL Server principal to write directly to that share, or **(2)** do the backup on the remote server and then copy the `.bak` file to a local drive/share. – marc_s Jan 02 '23 at 11:19

0 Answers0