Back Up a Database - SQL Server

This document provides comprehensive guidance on backing up a SQL Server database. Regular backups are crucial for data protection, disaster recovery, and maintaining data integrity.

Introduction

A database backup is a copy of your entire SQL Server database, including schema, data, transaction logs (depending on the backup type), and other objects. Backups enable you to restore your database to a specific point in time in case of hardware failure, data corruption, or accidental data deletion.

Before You Begin

Permissions

To create a backup, you typically need membership in the db_backupoperator fixed database role in the database that you are backing up. Alternatively, membership in the sysadmin or db_owner fixed server roles grants the necessary permissions.

Backup Options

Backup Types

SQL Server offers several backup types to suit different needs:

• Full Backup: Backs up the entire database. This is the foundation of any backup strategy.
• Differential Backup: Backs up only the data that has changed since the last full backup. This is faster than a full backup and requires a prior full backup to restore.
• Transaction Log Backup: Backs up the transaction log records that have been created since the last log backup (or full backup if no log backups have occurred). These are only available for databases in the FULL or BULK_LOGGED recovery model. Transaction log backups are essential for point-in-time recovery.

Backup Media

Backups can be written to various media:

• Disk (most common)
• Tape
• URL (Azure Blob Storage)

It is highly recommended to store backups on a separate physical location from the database server to protect against hardware failures.

Backup Compression

SQL Server can compress backups, which reduces the size of the backup files and can also speed up the backup process. Backup compression is enabled by default in SQL Server 2008 R2 and later.

Creating a Backup

Using SQL Server Management Studio (SSMS)

SSMS provides a user-friendly interface for creating backups:

1. Connect to the SQL Server instance in SSMS.
2. In Object Explorer, expand the Databases node.
3. Right-click the database you want to back up.
4. Select Tasks > Back Up...
5. In the "Back Up Database" dialog box:
   • Verify the Database name.
   • Choose the Backup type (Full, Differential, Transaction Log).
   • Select the destination for the backup (Disk or URL).
   • Click "Add..." to specify the backup file name and path.
   • Optionally, configure backup options such as compression, verification, and expiration.
6. Click OK to start the backup process.

Using Transact-SQL (T-SQL)

You can also perform backups using T-SQL statements. The primary command is BACKUP DATABASE.

Example: Full Backup to Disk

BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Example: Differential Backup to Disk

BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Example: Transaction Log Backup to Disk

BACKUP LOG [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Log.trn'
WITH NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Example: Full Backup with Compression

BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Full_Compressed.bak'
WITH COMPRESSION, NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Full Database Backup (Compressed)', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

Verifying a Backup

It's essential to verify your backups to ensure they are valid and can be used for restoration. You can use the RESTORE VERIFYONLY command in T-SQL or the "Verify backup when finished" option in SSMS.

Example: Verifying a Backup using T-SQL

RESTORE VERIFYONLY
FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak';
GO

Related Topics