DataZip

SQL Server DBA Tools: Database Backups

Mar 02, 2024By Tung Dang
Tung Dang

Backing up your SQL Server databases is a critical task to ensure the safety and integrity of your data. However, with various backup options and strategies available, it can be overwhelming to determine the best practices for your specific needs. In this post, we will demystify SQL Server backups and provide you with essential tips and strategies to help you streamline your backup process.

Query to List Last Backup Date for Every Database in a Server

declare @space table (
       DatabaseID int
       , DataSize float
       , [DataUsedPct] float
       , LogSize float
       , [LogUsedPct] float)

declare @sql nvarchar(2560)
declare @id int

while isnull(@id,1) <= ( select max(database_id) from sys.databases )
begin
       if exists (select 1 from sys.databases where database_id = isnull(@id,1) and has_dbaccess(name) = 1 and state_desc='ONLINE' )
       begin
              set @sql = N'USE [' + ( select name from sys.databases where database_id = isnull(@id,1) ) + '];
                           select ' + convert(nvarchar,isnull(@id,1)) + '
                           , totalsizedata = sum( case when type = 0 then size/128 else 0 end)
                           , totalsizedatausedpct = convert(decimal(4,1), sum( case when type = 0 then spaceused else 0 end) * 100.0 / sum(case when type = 0 then size else 0 end) )
                           , totalsizelog = sum( case when type = 1 then size/128 else 0 end)
                           , totalsizelogusedpct = convert(decimal(4,1), sum( case when type = 1 then spaceused else 0 end) * 100.0 / sum(case when type = 1 then size else 0 end) )
                     from (
                           select spaceused = fileproperty(name, ''spaceused''), *
                           from sys.database_files ) a1'

              insert into @space
              exec (@sql)
       end

       set @id = isnull(@id,1) + 1
end

select [DatabaseID] = d.database_id
       , [Database] = d.[Name]
       , [Status] = d.state_desc
       , [CreateDate] = convert(char(10),d.create_date,101) + right(convert(char(19),d.create_date,100),8)
       , [Recovery] = d.Recovery_Model_Desc
       , [LastFullBackup] = ( select convert(char(10),max(backup_finish_date),101) + right(convert(char(19),max(backup_finish_date),100),8) from msdb.dbo.backupset where database_name = d.name and type = 'D' )
       , [LastDiffBackup] = ( select convert(char(10),max(backup_finish_date),101) + right(convert(char(19),max(backup_finish_date),100),8) from msdb.dbo.backupset where database_name = d.name and type = 'I' )
       , [LastLogBackup] = ( select convert(char(10),max(backup_finish_date),101) + right(convert(char(19),max(backup_finish_date),100),8) from msdb.dbo.backupset where database_name = d.name and type = 'L' )
       , [DataSize] = case when st.Datasize/1024/1024 >= 1 then convert(varchar,convert(decimal(8,1),st.DataSize/1024/1024.0)) + ' TB'
                                  when st.Datasize/1024 >= 1 then convert(varchar,convert(decimal(8,1),st.DataSize/1024.0)) + ' GB'
                                  else cast(st.DataSize as varchar) + ' MB' end
       , [DataUsedPct]
       , [LogSize] = case when st.Logsize/1024/1024 >= 1 then convert(varchar,convert(decimal(8,1),st.Logsize/1024/1024.0)) + ' TB'
                                  when st.Logsize/1024 >= 1 then convert(varchar,convert(decimal(8,1),st.Logsize/1024.0)) + ' GB'
                                  else cast(st.Logsize as varchar) + ' MB' end
       , [LogUsedPct]
from sys.databases d
       left join @space st on d.database_id = st.databaseid
order by d.[Name]

Query to List Backup History for a Database

SELECT
       s.database_name,
       m.physical_device_name,
       CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
       CAST(DATEDIFF(second, s.backup_start_date,
       s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
       s.backup_start_date,
       CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
       CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
       CASE s.[type] WHEN 'D' THEN 'Full'
              WHEN 'I' THEN 'Differential'
              WHEN 'L' THEN 'Transaction Log'
              END AS BackupType,
       s.server_name,
       s.recovery_model
FROM msdb.dbo.backupset s
       JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
       AND s.[type] <> 'L'
ORDER BY 1,2, backup_start_date DESC, backup_finish_date

Types of SQL Server Backups

There are several types of backups you can perform in SQL Server, each serving a different purpose:

  • Full Backup: This type of backup creates a complete copy of the database, including all data and objects.

         For backing up to local drive:
         backup database [master] to disk = 'u:\Backup\master_20240308_FULL.bak' with STATS, CHECKSUM, COMPRESSION;

         For backing up to azure storage using key:
         backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_FULL.bak' with CREDENTIAL = 'SQLAzureStorage', STATS, CHECKSUM, COMPRESSION;

             For backing up to azure storage using SAS:
         backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_FULL.bak' with STATS, CHECKSUM, COMPRESSION;

         For backing up to azure fileshare:
         backup database [master] to disk = 'u:\Backup\master_20240308_FULL.bak' with STATS, CHECKSUM, COMPRESSION;
    • Differential Backup: This backup captures only the changes made since the last full backup, reducing the backup size and time.

           For backing up to local drive:
           backup database [master] to disk = 'u:\Backup\master_20240308_DIFF.bak' with STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;

           For backing up to azure storage using key:
           backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_DIFF.bak' with CREDENTIAL = 'SQLAzureStorage', STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;

           For backing up to azure storage using SAS:
           backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_DIFF.bak' with STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;

           For backing up to azure fileshare:
           backup database [master] to disk = 'u:\Backup\master_20240308_DIFF.bak' with STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;

    • Transaction Log Backup: This backup captures all transactions since the last transaction log backup, allowing for point-in-time recovery.

           For backing up to local drive:
           backup log [master] to disk = 'u:\Backup\master_20240308_0315.trn' with FORMAT, STATS, CHECKSUM, COMPRESSION;

           For backing up to azure storage using key:
           backup log [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_0315.trn' with CREDENTIAL = 'SQLAzureStorage', FORMAT, STATS, CHECKSUM, COMPRESSION;

           For backing up to azure storage using SAS:
           backup log [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_0315.trn' with FORMAT, STATS, CHECKSUM, COMPRESSION;

           For backing up to azure fileshare:
           backup log[master] to disk = 'u:\Backup\master_20240308_0315.trn' with STATS, CHECKSUM, COMPRESSION;

Choosing the right combination of these backup types is crucial for a robust backup strategy.

Backup Frequency and Retention

Deciding how often to perform backups and how long to retain them is a key aspect of backup planning. Consider the following factors:

  • Frequency of data changes
  • Recovery point objectives (RPO)
  • Compliance and regulatory requirements

Establishing a backup schedule and retention policy that aligns with your business needs is essential to ensure data availability and minimize data loss.

Backup Storage and Security

Storing your backups securely is as crucial as creating them. Consider the following best practices:

  • Store backups on separate disks or network locations to prevent data loss in case of hardware failure.
  • Implement encryption to protect sensitive data in backups.
  • Regularly test your backup and restore processes to ensure data recoverability.

By following these practices, you can enhance the security and reliability of your backup infrastructure.

Create SQL Credential - Use this credential to backup to an Azure storage account with a storage key.

CREATE CREDENTIAL [Any credential name]
WITH IDENTITY = '[Azure storage account name]' 
     , SECRET = '[Azure storage account key]'

Shared Access Signature - Safer than a storage key since permissions are more granular and you can set an expiration date.  Use this to back up to an Azure storage account.

USE master
CREATE CREDENTIAL [https://<storage account name>.blob.core.windows.net/<container name>]
    WITH IDENTITY='SHARED ACCESS SIGNATURE' -- ***this is a mandatory string and do not change it.
    , SECRET = 'sharedaccesssignature' -- ***this is the shared access signature token generated from azure storage account
GO

*** The SHARED ACCESS SIGNATURE secret should not have the leading ?.

Automating Backup Tasks

Automating backup tasks can help streamline the backup process and reduce the risk of human error. SQL Server provides built-in tools like SQL Server Agent for scheduling backups and maintenance tasks.

sql server backups

By setting up automated backup jobs, you can ensure that backups are performed consistently and according to your defined schedule.

Monitoring and Alerts

Regularly monitoring your backup processes and setting up alerts for backup failures are essential steps to maintain the integrity of your backups. Utilize SQL Server's monitoring tools to track backup status and receive notifications in case of failures.

Conclusion

Implementing a robust backup strategy is crucial for safeguarding your SQL Server databases against data loss and ensuring business continuity. By understanding the different types of backups, establishing a backup schedule, securing your backups, automating backup tasks, and monitoring the process, you can effectively manage your backup operations and protect your valuable data.