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
.zip
file. - 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 SqlServerDatabaseBackup
Then 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
.bak
file. - Create a zip archive with the
.bak
file. - Read the zip into memory for download.
- Delete both
.bak
and.zip
files 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!