Managing a production SQL Server database means you can’t afford to skip backups. But downloading .bak files manually or relying on daily scripts isn’t ideal — especially if you’re building modern systems with user-facing APIs. Recently, I worked on a small utility project to automate SQL Server database backups and offer them as downloadable ZIP files via an ASP.NET Core API.
It’s simple, secure, and production-ready. In this article, I’ll walk you through how I built it — including real code, logging, file handling, and zip compression. If you’re a backend dev working with .NET and SQL Server, I hope this helps you save time and keep your databases safer.
Why Build This?
I’ve seen a lot of manual processes in local dev teams and small businesses. Sometimes people go into SQL Server Management Studio, right-click the database, export .bak, then manually zip it and send it somewhere.
That might work in a pinch, but I wanted:
- A one-click backup download via API.
- Automatic compression into a
.zipfile. - Temporary files cleaned up automatically.
- A log trail for debugging and audit.
So, I decided to build an endpoint: GET /api/databasebackup/download that does all of that in one shot.
Prerequisites
To follow along, you’ll need:
- SQL Server running (locally or on a network).
- .NET 6 or newer (I used .NET 7).
- Basic understanding of Web API development.
- Access credentials for your SQL Server.
- A folder path on the server for temporary file storage.
Project Setup
I created a new ASP.NET Core Web API project using the command line:
dotnet new webapi -n SqlServerDatabaseBackup
cd SqlServerDatabaseBackupThen I added logging support (built-in by default) and configured my controller.
The Final API Logic
Here’s a full breakdown of what happens when the API is called:
- Extract the database name from the connection string.
- Generate a timestamped filename.
- Use a SQL query to back up the database to a
.bakfile. - Create a zip archive with the
.bakfile. - Read the zip into memory for download.
- Delete both
.bakand.zipfiles to clean up. - Log each step or failure.
Final Code
You can find the full code on GitHub: SqlServerDatabaseBackupAPI. Below is the relevant snippet:
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using System.IO.Compression;
namespace SqlServerDatabaseBackup.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class DatabaseBackupController : ControllerBase
{
private readonly string _connectionString = "Server=TOUHID-PC; Database=VATNBR_WEB_MT;Trusted_Connection=False;ConnectRetryCount=0;User Id=sa; Password=data;Encrypt=False;TrustServerCertificate=True;";
private readonly string _backupFolder = "C:\\DatabaseBackups";
private readonly ILogger _logger;
public DatabaseBackupController(ILogger logger)
{
_logger = logger;
}
[HttpGet("download")]
public async Task DownloadZippedBackup()
{
var builder = new SqlConnectionStringBuilder(_connectionString);
string databaseName = builder.InitialCatalog;
string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
string bakFileName = $"{databaseName}_{timestamp}.bak";
string zipFileName = $"{databaseName}_{timestamp}.zip";
string bakFilePath = Path.Combine(_backupFolder, bakFileName);
string zipFilePath = Path.Combine(_backupFolder, zipFileName);
try
{
_logger.LogInformation("Starting database backup process for '{DatabaseName}'", databaseName);
if (!Directory.Exists(_backupFolder))
{
Directory.CreateDirectory(_backupFolder);
_logger.LogInformation("Created backup folder: {BackupFolder}", _backupFolder);
}
// Step 1: Backup the database
_logger.LogInformation("Backing up database to file: {BakFilePath}", bakFilePath);
BackupDatabase(_connectionString, databaseName, bakFilePath);
// Step 2: Create zip file
_logger.LogInformation("Creating zip archive: {ZipFilePath}", zipFilePath);
using (var zipToOpen = new FileStream(zipFilePath, FileMode.Create))
using (var archive = new ZipArchive(zipToOpen, ZipArchiveMode.Create))
{
archive.CreateEntryFromFile(bakFilePath, bakFileName, CompressionLevel.Optimal);
}
// Step 3: Read zip into memory
_logger.LogInformation("Reading zip file into memory");
var memoryStream = new MemoryStream();
using (var zipStream = new FileStream(zipFilePath, FileMode.Open, FileAccess.Read))
{
await zipStream.CopyToAsync(memoryStream);
}
memoryStream.Position = 0;
// Step 4: Delete temp files
_logger.LogInformation("Deleting temporary files");
System.IO.File.Delete(bakFilePath);
_logger.LogInformation("Deleted .bak file: {BakFilePath}", bakFilePath);
System.IO.File.Delete(zipFilePath);
_logger.LogInformation("Deleted .zip file: {ZipFilePath}", zipFilePath);
_logger.LogInformation("Database backup and zip completed successfully for '{DatabaseName}'", databaseName);
return File(memoryStream, "application/zip", zipFileName);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error occurred during database backup");
// Cleanup on failure
if (System.IO.File.Exists(bakFilePath))
{
System.IO.File.Delete(bakFilePath);
_logger.LogWarning("Deleted leftover .bak file: {BakFilePath}", bakFilePath);
}
if (System.IO.File.Exists(zipFilePath))
{
System.IO.File.Delete(zipFilePath);
_logger.LogWarning("Deleted leftover .zip file: {ZipFilePath}", zipFilePath);
}
return BadRequest($"Error: {ex.Message}");
}
}
private void BackupDatabase(string connectionString, string databaseName, string backupFilePath)
{
using (var connection = new SqlConnection(connectionString))
{
string query = $@"BACKUP DATABASE [{databaseName}]
TO DISK = N'{backupFilePath}'
WITH FORMAT, INIT, NAME = N'{databaseName}-Full Backup'";
using (var command = new SqlCommand(query, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}
_logger.LogInformation("Database '{DatabaseName}' successfully backed up to '{BackupFilePath}'", databaseName, backupFilePath);
}
}
}
Key Points to Note
1. Temporary Files Are Cleaned Up
No one wants their server storage filled with leftover .bak or .zip files. That’s why I ensured the process deletes everything after serving the file to the client.
2. No Hardcoded Database Names
The database name is pulled from the connection string using SqlConnectionStringBuilder, so if you change your connection string in production or dev, the code still works as expected.
3. Built-in Logging
If anything fails during backup or zipping, it logs a detailed message using the built-in ILogger system. It’s easy to monitor using console output, file logging, or services like Serilog or Application Insights.
When Should You Use This?
You might find this useful if:
- You’re a solo dev or small team managing internal tools.
- You want to expose a backup endpoint for trusted admin users.
- You need a quick way to snapshot databases before deployments.
- You’re building admin panels or automation tools.
Warning: Don’t expose this API publicly unless it’s protected by authentication and authorization. Otherwise, anyone could download your entire database.
Improvements You Can Add
- 🔐 Add authentication (JWT, API key, or Identity).
- 🗂 Allow selecting which database via query string (with security).
- 📦 Add password-protection to the zip archive (use third-party zip libraries).
- 📤 Upload the zip to cloud storage (like Azure Blob, S3, or Google Drive).
- 📅 Schedule backups with Hangfire or a Windows service.
Conclusion
This was one of those fun utility projects where you build something once and it saves hours of work later. I no longer have to worry about forgetting database backups — a single click on the endpoint handles everything.
If you’re managing SQL Server databases and want a quick, reliable way to download backups, feel free to clone the project from my GitHub:
👉 SqlServerDatabaseBackupAPI on GitHub
Let me know if you implement it, and feel free to suggest features or improvements!
