How to Backup a Database in SQL Server

In this blog post I’m sharing a demo for how to backup databases in Microsoft SQL Server.

As a Production SQL DBA, having backups of your data is just as important as keeping your SQL Server online/available to serve its applications and users. If you don’t have confidence you have backups of data, then it’s your job to investigate & resolve it. You never want to be in a position where you’ve lost business critical data, obviously.

Topics Covered:
1. SQL Server Full Database Backups
2. SQL Server Transaction Log Backups
3. SQL Server Differential Backups
4. Additional Notes / Conclusion

Before performing changes or running commands on a Production SQL Server, we should always refer to Microsoft’s official documentation, here’s their Database Backups Overview page.

---

1. SQL Server Full Database Backups

A Full Database Backup in SQL Server captures the entire database, including all the data up to the time the backup is taken. It serves as your primary restore point.

Before starting, make sure there’s enough space on the disk where the backup will be stored. If you use the compression option, you’ll save a lot of space – we can calculate the compression ratio of a backup to help with estimating disk space requirements for VLDBs (very large databases).

To perform a Full Database Backup with Compression, run the following:
Amend the database name and change the backup location to your directory.

-- Run full database backup with compression
BACKUP DATABASE [demoBlog] TO DISK = N'd:\D:\mssql_backups\demoBlog_full_01082022_1300.bak' WITH COMPRESSION, STATS;

Note: The time it takes for a backup to complete on the database size and other factors. For large databases, backups can take several hours or even days to complete if not optimized. Consider using a SQL Script to show how long a backup has left to complete.

To keep your important servers secure from data loss, we should have a scheduled job to run Full Database Backups on our SQL Server. Having Full Backups run daily is a common approach, followed by a clean-up job to remove backups older than 30 days.

2. SQL Server Transaction Log Backups

Transaction Log (TLog) Backups in SQL Server records the changes made to the database since its last Full Backup. TLog backups are needed for achieving point in time database recovery, which is an essential part of having proper Disaster Recovery (DR) plans in place.

To perform a TLog Backup, run the following:

-- Perform log backup for database
BACKUP LOG [demoBlog] TO DISK = N'd:\mssql_backups\demoBlog_tlog_21082022_2130.bak' WITH COMPRESSION, STATS;

Transaction Log backups need to run often, ranging from hourly to about every 10 minutes depending on your own Disaster Recovery requirements. Keeping about a week of TLog backup files would give great flexibility for recovering data loss for a specific time and day.

On non-Production environments, you might not need to use TLog backups.

3. SQL Server Differential Backups

A Differential (Diff) Database Backup stores only the data that has changed since the last Full Backup, which makes it faster to run compared to a Full Backup. Differential backups are less common in production but can be useful for quick interim backups.

Run a Diff Backup with the following command:

-- Run differential db backup
BACKUP DATABASE [demoBlog] TO DISK = N'd:\mssql_backups\demoBlog_diff_21082022_2133.bak' WITH DIFFERENTIAL, COMPRESSION, STATS;

Key Point: The size of a Differential Backup depends on how much data has changed since the last Full Backup.

4. Conclusion

Backing up your SQL Server databases is crucial for maintaining data integrity and ensuring disaster recovery readiness.

To optimize your backup strategy, consider these best practices:

• Schedule Regular Backups: Implement a backup schedule that aligns with your business needs, incorporating Full, Transaction Log, and Differential backups as necessary.
• Test Restores Frequently: Regularly verify that backups can be successfully restored in a non-production environment.
• Monitor Backup Sizes and Durations: Track backup times and storage requirements to prevent unexpected issues.
• Use Compression: Enable backup compression to save space and improve efficiency.
• Run Backups During Off-Peak Hours: Schedule backups when database activity is low to minimize performance impact.
• Back Up to Multiple Locations: Store backups in different locations (e.g., on-premises and cloud) to safeguard against hardware failures or disasters.
• Leverage Readable Secondary Replicas: If using Availability Groups (AG) with readable secondaries, offload backup tasks to reduce the load on your primary server.

Following these practices will help you ensure that your backup strategy is robust, efficient, and aligned with your business recovery objectives.

Security/Permissions: When managing backups, as a Database Administrator we should be monitoring to ensure the backup chain remains intact. We achieve this by following the Principal of Least Privilege (PoLP) when we set permissions for all SQL users so that only specific users can run backup commands. We also do avoid breaking the chain with manual Full Backups by using the COPY_ONLY option.

Additionally, always verify the last backup date before initiating a new backup. If backups are part of a scheduled disaster recovery plan, they should already be occurring at regular intervals.