Best Practices for SQL Server Backup and Restore Operations

Introduction

SQL Server Backup and Restore operations are critical components of any data management strategy. Regular backups of your databases ensure that you can recover your data and minimize downtime in the event of a disaster. However, not all backup and restore strategies are created equal. This article will discuss some best practices for SQL Server backup and restore operations, along with examples.

How to Define a Backup Strategy?

A backup strategy is a plan that outlines how frequently you will back up your databases, what type of backups you will perform, where you will store the backups, and who will be responsible for managing the backups. Your backup strategy should take into account your recovery time objectives (RTOs) and recovery point objectives (RPOs), which are the maximum acceptable downtime and data loss, respectively, for your organization.

Here is an example of how you can define a backup strategy using code.

USE [master]
GO

-- Create a backup device on disk
EXEC sp_addumpdevice 'disk', 'MyBackupDevice', 'C:\Backup\MyBackupFile.bak'
GO

-- Create a full backup job
DECLARE @backupName NVARCHAR(128)
SET @backupName = N'MyDatabase-FullBackup'

DECLARE @backupFile NVARCHAR(260)
SET @backupFile = N'C:\Backup\MyDatabase-FullBackup.bak'

BACKUP DATABASE [MyDatabase]
TO DISK = @backupFile
WITH FORMAT, INIT, NAME = @backupName
GO

-- Create a differential backup job
DECLARE @diffBackupName NVARCHAR(128)
SET @diffBackupName = N'MyDatabase-DiffBackup'

DECLARE @diffBackupFile NVARCHAR(260)
SET @diffBackupFile = N'C:\Backup\MyDatabase-DiffBackup.bak'

BACKUP DATABASE [MyDatabase]
TO DISK = @diffBackupFile
WITH DIFFERENTIAL, NAME = @diffBackupName
GO

-- Create a transaction log backup job
DECLARE @logBackupName NVARCHAR(128)
SET @logBackupName = N'MyDatabase-LogBackup'

DECLARE @logBackupFile NVARCHAR(260)
SET @logBackupFile = N'C:\Backup\MyDatabase-LogBackup.trn'

BACKUP LOG [MyDatabase]
TO DISK = @logBackupFile
WITH NAME = @logBackupName
GO

Here we create a backup device on disk, which points to a specific location where the backups will be stored. Then, we define three backup jobs: a full backup job, a differential backup job, and a transaction log backup job. By defining a backup strategy using code like this, you can automate the backup process and ensure that your backups are consistent and reliable.

Test Your Backup and Restore Strategy

Performing backups is only half the battle; you must also test your restore strategy to ensure that you can recover your data in the event of a disaster. Regularly testing your backup and restore strategy can help identify any issues or gaps in your strategy.

Here is an example of how you can test your backup and restore strategy using code.

-- Create a test database to restore to
CREATE DATABASE MyDatabase_Test
GO

-- Restore the full backup
RESTORE DATABASE MyDatabase_Test
FROM DISK = 'C:\Backup\MyDatabase-FullBackup.bak'
WITH NORECOVERY
GO

-- Restore the differential backup
RESTORE DATABASE MyDatabase_Test
FROM DISK = 'C:\Backup\MyDatabase-DiffBackup.bak'
WITH NORECOVERY
GO

-- Restore the transaction log backup(s)
RESTORE LOG MyDatabase_Test
FROM DISK = 'C:\Backup\MyDatabase-LogBackup.trn'
WITH NORECOVERY
GO

-- Recover the database
RESTORE DATABASE MyDatabase_Test WITH RECOVERY
GO

In the above code, we restore the full backup using the RESTORE DATABASE command with the NORECOVERY option; this leaves the database in a state where we can restore additional backups. Next, we restore the differential backup using the same RESTORE DATABASE command with the NORECOVERY option; this restores any changes made since the full backup was taken.

Finally, we restore the transaction log backup(s) using the RESTORE LOG command with the NORECOVERY option; this restores any additional changes made since the differential backup was taken. After all, the backups are restored, we recover the database using the RESTORE DATABASE command with the RECOVERY option. This brings the database online and makes it available for use.

Monitor Backup and Restore Operations

Monitoring your backup and restore operations can help you identify issues before they become critical. You can use SQL Server's built-in backup and restore monitoring features or third-party monitoring tools to track backup and restore progress, success rates, and errors.

Here is an example of monitoring your backup and restoring operations using code.

-- Monitor the progress of a backup operation
SELECT session_id AS SPID,
    command AS BackupCommand,
    start_time AS StartTime,
    percent_complete AS PercentComplete,
    estimated_completion_time AS EstimatedCompletionTime,
    total_elapsed_time AS TotalElapsedTime,
    DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS EstimatedEndTime
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%'

-- Monitor the progress of a restore operation
SELECT session_id AS SPID,
    command AS RestoreCommand,
    start_time AS StartTime,
    percent_complete AS PercentComplete,
    estimated_completion_time AS EstimatedCompletionTime,
    total_elapsed_time AS TotalElapsedTime,
    DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS EstimatedEndTime
FROM sys.dm_exec_requests
WHERE command LIKE 'RESTORE%'

-- Monitor the backup and restore history
SELECT backup_start_date AS BackupStartDate,
    backup_finish_date AS BackupFinishDate,
    type AS BackupType,
    backup_size AS BackupSize,
    recovery_model AS RecoveryModel,
    database_name AS DatabaseName,
    user_name AS UserName
FROM msdb.dbo.backupset
ORDER BY backup_start_date DESC

In the above code, we use the sys.dm_exec_requests dynamic management view to monitor the progress of backup and restore operations and use the msdb.dbo.backupset table, we can monitor the backup history.

Use Compression and Encryption

Compressing your backups can help reduce the size of your backups, reducing backup and restore times and storage costs. Encrypting your backups can help protect sensitive data from unauthorized access.

Here is an example of how you can use compression and encryption with code.

-- Use compression in backup operation
BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backup\MyDatabase.bak'
WITH COMPRESSION

-- Use encryption in backup operation
BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backup\MyDatabase.bak'
WITH PASSWORD = 'MyPassword123'

-- Use compression and encryption in backup operation
BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backup\MyDatabase.bak'
WITH COMPRESSION, PASSWORD = 'MyPassword123'

-- Use compression in restore operation
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backup\MyDatabase.bak'
WITH REPLACE, COMPRESSION

-- Use encryption in restore operation
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backup\MyDatabase.bak'
WITH REPLACE, PASSWORD = 'MyPassword123'

-- Use compression and encryption in restore operation
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backup\MyDatabase.bak'
WITH REPLACE, COMPRESSION, PASSWORD = 'MyPassword123'

In the above code, we use the WITH COMPRESSION clause to enable compression and WITH PASSWORD the clause to enable encryption during the backup and restore operation.

Store Backups in Multiple Locations

Storing backups in multiple locations can help protect against local disasters such as fires, floods, and theft. You can store backups on-premises, in the cloud, or on removable media such as tapes or hard drives.
For example, you may decide to store backups on a separate server in your data center and in the cloud to protect against local disasters.

Here is an example of how you can store backups on a separate server in your data center and the cloud using SQL Server.

-- Store backup in primary location
BACKUP DATABASE MyDatabase
TO DISK = 'D:\Backup\MyDatabase.bak'
WITH COMPRESSION

-- Store backup in secondary location
BACKUP DATABASE MyDatabase
TO DISK = 'E:\Backup\MyDatabase.bak'
WITH COMPRESSION

-- Store backup on a separate server in your data center
BACKUP DATABASE MyDatabase
TO DISK = '\backupserver\backup\MyDatabase.bak'
WITH COMPRESSION

-- Store backup in the cloud using Azure Blob Storage
BACKUP DATABASE MyDatabase
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
WITH CREDENTIAL = 'MyCredential', COMPRESSION

The first two queries create two separate backup files of the MyDatabase database, one in the D:\Backup directory and another in the E:\Backup directory. In the third query, we are storing the MyDatabase backup file on a separate server in our data center. The backup file is saved to the network share of the backup server located at \backupserver\backup.

In the last query, we are storing the MyDatabase backup file in the cloud using Azure Blob Storage. The backup file is saved to the blob container named "mycontainer" in the "mystorageaccount" storage account. We are using the WITH CREDENTIAL clause to specify the name of the Azure Storage Credential to be used to access the storage account.

To use Azure Blob Storage as a backup location, you must create an Azure Storage Account and Blob Container and then create a SQL Server Credential with the access key to the storage account. You can use the Azure portal to create the storage account and blob container.

Conclusion

SQL Server backup and restore operations are critical components of any data management strategy. By following these best practices, you can ensure that your backups are recoverable, that your restore strategy is effective, and that your data is protected against disasters. Remember to define a backup strategy, test your backup and restore strategy, use multiple backup types, monitor backup and restore operations, use compression and encryption, and store backups in multiple locations.