Backup SQL Database is an essential activity for database administrators.
Table of Contents
Introduction
Database backup is an important activity to guarantee the availability and integrity of your data. This extensive book covers prerequisites, database backup types, T-SQL commands, syntax, examples, and FAQs. We also explore using SQL Server Agent and PowerShell scripts for backup automation in various settings, such as Azure SQL databases, standalone servers, cluster environments, and Always On setups.
Requirements for Backup SQL Database
To guarantee that the procedure proceeds without a hitch and that the backup can be utilized efficiently in a disaster, performing a database backup in SQL Server calls for a few setups and preparations. The following are the necessary prerequisites:
Adequate Hard Drive Space
Ensure the drive where the backup will be kept has adequate disk space. The database backup file size should be accommodated in the necessary space.
--Verifying the Disk Capacity using the below command
EXEC xp_fixeddrives;Sufficient Permissions
The user doing the backup needs to be authorized to do so. It would usually be a member of the db_backupoperator role for that particular database or a user with the sysadmin role.
--Verifying the Permissions of Users on the server using the below command
SELECT @@ServerName AS [ServerName],
IS_MEMBER('db_backupoperator') AS [IsMemberOfBackupOperator],
IS_SRVROLEMEMBER('sysadmin') AS [IsMemberOfSysAdmin];Specified Location for Backup SQL Database
Select a trustworthy and safe place to keep your backup files. It could be an external storage device, a network share, or a local disk. Make sure the SQL Server can access the path.
Database Backup Strategy
Using the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) as a guide, clearly define a backup plan. Choose the frequency and types of backups (Full, Differential, and Transaction Log).
Recovery Model of Databases
Ensure the database utilizes the Full, Simple, or Bulk-Logged recovery model corresponding to your backup plan. The recovery model affects your ability to retrieve data and controls how transaction log backups are managed.
--To examining the Database Recovery Model for database on the server
SELECT @@ServerName AS [ServerName],
GETDATE() AS [CurrentDateTime],
Name AS [DatabaseName],
Recovery_Model_desc AS [DatabaseRecoveryModel]
FROM sys.databases;
-- We can use the below WHERE condition to get the details of a particular database
WHERE name = 'NameOfUserDatabase';
The SQL Server Agent Service
If you intend to use SQL Server Agent jobs to automate backups, make sure the SQL Server Agent service is up and functioning.
--Verifying the SQL Server Agent's Operation on the server
EXEC xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent';
Configuring the Network
If the backups are kept at a network location, ensure that the network parameters permit efficient data transfer and that the SQL Server service account has written permissions to share the network.
Tested Procedures for Backup and Restore
We need to capture and test backups regularly to ensure that backups are made correctly and can be restored without problems. Periodic restorations to a test environment are required for this.
Observation and Warnings
Set up warning and monitoring systems to inform you of problems or failures with your backups. Third-party tools, including SQL Server Management Studio (SSMS), can send alerts.
Optional Compression and Encryption
Consider turning on backup encryption and compression to protect the backup files and conserve capacity. It can be crucial for sensitive data and extensive databases.
Database Backup Types
Database backups can be made in SQL Server to guarantee data security and facilitate data recovery. Every backup has a distinct function and can be integrated into a strong backup plan. The main categories of database backups and their corresponding T-SQL procedures are as follows:
Full Backup SQL Database
A full backup captures the complete database at a particular moment in time. It contains all the information required to restore the database, including system tables, transaction logs, and data.
--T-SQL Query to take Full Database Backup of a database In SQL Server:
BACKUP DATABASE [NameOfDatabase]
TO DISK='K:\DBBackups\Full\NameOfDatabase_Full_<CurrentDateTime>.bak'
WITH STATS=5, COMPRESSION;Differential Backup SQL Database
Differential backup takes the data that has changed since the last complete backup. It is typically smaller and executed more quickly than a full backup.
--T-SQL Query to take Differential Backup of a database SQL Database:
BACKUP DATABASE [NameOfDatabase]
TO DISK='K:\DBBackups\Diff\NameOfDatabase_Diff_<CurrentDateTime>.bak'
WITH STATS=5, COMPRESSION;Transaction Logs Backup SQL Database
A transaction log backup captures every transaction since the last backup. It is important for databases that use the Bulk-Logged or Full recovery approach.
--T-SQL Query to take T-Log Backup of a database SQL Database:
BACKUP LOG [NameOfDatabase]
TO DISK='K:\DBBackups\T-LOG\NameOfDatabase_TLog_<CurrentDateTime>.trn'
WITH STATS=5, COMPRESSION;File and Filegroup Backup SQL Database
You can create backup SQL database of files or filegroups using file and filegroups. This can be helpful when backing up a huge database is not practicable.
--T-SQL Query to take File Backup of a database In SQL Server:
BACKUP DATABASE [NameOfDatabase]
File = 'DatabaseFileName' DISK='K:\DBBackups\Full\NameOfDatabase_File_<CurrentDateTime>.bak'
WITH STATS=5, COMPRESSION;
--T-SQL Query to take Filegroup Backup of a database In SQL Server:
BACKUP DATABASE [AdventureWorks2019]
FILEGROUP = 'ReadOnly'
TO DISK = 'E:\DBBackups\Full\AdventureWorks2019_FileGroup.bak'
WITH STATS=5, COMPRESSION;
Copy-Only Backup SQL Database
One unique kind of full backup that doesn’t interfere with the standard backup sequence is a copy-only backup. With it, backups for particular uses can be made without interfering with the standard backup sequence.
--T-SQL Query to take Copy_Only Full Backup of a database In SQL Server:
BACKUP DATABASE [NameOfDatabase]
TO DISK='K:\DBBackups\Full\NameOfDatabase_CopyOnlyFull_<CurrentDateTime>.bak'
WITH STATS=5, COPY_ONLY, COMPRESSION;
Partial Backup SQL Database
Databases with read-only filegroups are meant for partial backups. They create backups of every read-write filegroup, the primary filegroup, and, if desired, one or more read-only filegroups.
--T-SQL Query to take Partial Backup of a database In SQL Server:
DATABASE BACKUP [NameOfDatabase]
TO DISK = 'K:\DBBackups\Full\NameOfDatabase_PartialWithReadOnlyFG.bak',
FILEGROUP = 'NameOfReadOnlyFileGroup'
WITH STATS=5, COPY_ONLY, COMPRESSION;Best Practices for Database Backup
Take into account the following best practices to make sure your backup plan is strong and dependable:
Frequent Backups: Depending on your recovery goals, schedule regular full, differential, and log backups.
Offsite Storage: To guard against calamities peculiar to a particular site, store backups elsewhere or in a secure cloud storage account.
Backup Testing: Test your backups on a regular basis by restoring them to make sure they work.
Encryption: Encrypt backup files to keep private information safe from prying eyes.
Documentation: Keep thorough records of all the steps you take for backup and recovery.
Monitoring: Utilize monitoring tools to receive alerts when there are problems or failures with your backup.
SQL Server Database Backup Background Process
In backup SQL databases are really essential for guaranteeing disaster recovery, availability, and data integrity. Database administrators may optimize performance, preserve consistency, and guarantee that backups are executed successfully and efficiently by being aware of the background processes involved in SQL Server backups.
The background process of database backup are given below for clear understanding
Getting ready
To guarantee consistency, SQL Server freezes I/O activities to prepare the database for a full backup. Creating a point-in-time snapshot entails briefly locking the database.
Reading Pages in a Database
System tables, user tables, indexes, and other database objects are among the data pages that SQL Server reads from the database. Database activities can continue in parallel thanks to the non-blocking nature of this procedure.
Composing Data for a Backup Device
Sequentially, the read data pages are written to the backup media (disk, tape, or URL). Multiple buffers are used by SQL Server to handle this procedure, guaranteeing effective data transfer.
Maintaining metadata records
The MSDB system database contains information about the backup, including the start and end times, type, and file location. The management of backups and restoration processes makes use of this metadata.
Finalization
Backup SQL database records the completion status and releases any locked locks once all data pages have been backed up. The database is restored to its regular operating state when the success or failure of the backup procedure is recorded.
Background of the Differential Backup Process
Finding the Edited Pages
For every database file, SQL Server keeps track of a Differential Change Map (DCM). Extentsis have changed since the last complete backup is monitored by the DCM.
Examining the Revised Pages
Only the data pages indicated in the DCM are read by SQL Server while doing a differential backup. By doing this, less data is read and written than when performing a full backup.
Composing Data for a Backup Device
The backup device receives the updated data pages. Though it uses a smaller data set, this procedure is comparable to the complete backup method.
Maintaining metadata records
The MSDB database contains references to the corresponding full backup along with metadata related to the differential backup.
Transaction Log Backup’s Background Process
Gathering log records
Every record created in the transaction log since the last backup is captured by SQL Server. Both committed and uncommitted transactions are included in this.
Composing Data for a Backup Device
The backup device is written using the recorded log records. Generally speaking, transaction log backups are quicker and smaller than full or differential backups.
Cutting the Log Short
SQL Server truncates the idle section of the transaction log to make room for new transactions after the transaction log backup is finished.
Maintaining metadata records
The msdb database stores the metadata for the transaction log backup, guaranteeing that the log backup sequence is preserved for point-in-time recovery.
Examples
A few examples are given below for better understanding
Example 1: How to take File / Filegroup Backup in SQL Server
Backing up specific database files/filegroups is helpful for large databases where a full backup is not feasible.
-- How to take File / Filegroup Backup of a database in SQL Server
BACKUP DATABASE [NameOfUserDatabase]
FILE = 'NameOfUserFileGroup'
TO DISK = 'K:\DBBackups\NameOfUserDatabase_NameOfUserFileGroup.bak'
WITH NAME = 'Filegroup Backup of UserDatabase',
DESCRIPTION = 'Filegroup backup Executed on June 15, 2024 at 1:AM',
STATS = 5;Example 2: How to take Striped Backup in SQL Server
The striped database backup writes the backup to multiple files simultaneously, which can increase backup performance by dispersing the I/O burden.
-- How to take Striped Backup of a database in SQL Server
BACKUP DATABASE [NameOfUserDatabase]
TO DISK = 'K:\DBBackups\NameOfUserDatabase_Strip1.bak',
DISK = 'K:\DBBackups\NameOfUserDatabase_Strip2.bak'
WITH FORMAT,
NAME = 'Striped Full Database Backup of UserDatabase',
DESCRIPTION = 'Striped full Database backup Executed on June 15, 2024 at 1:AM',
STATS = 5, COMPRESSION;Example: Schedule Database Backup within SQL Server Agent & automate it.
Scheduling a backup SQL database with SQL Server Agent guarantees database backups are executed regularly without any manual involvement.
In the First Step, create a new SQL Server Agent job:
USE msdb;
GO
EXEC dbo.sp_add_job
@job_name = N'SQL DBA Maintenance Job - Full DB Backup';
GO
EXEC sp_add_jobstep
@job_name = N'SQL DBA Maintenance Job - Full DB Backup',
@step_name = N'User Database Backup',
@subsystem = N'TSQL',
@command = N'BACKUP DATABASE [NameOfUserDatabase] TO DISK = ''K:\DBBackups\NameOfUserDatabase_Full.bak'' WITH FORMAT, NAME = ''Daily Full Database Backup'', DESCRIPTION = ''Daily Full Database Backup'', STATS = 5, COMPRESSION;',
@retry_attempts = 5,
@retry_interval = 5;
GO
EXEC sp_add_schedule
@schedule_name = N'Daily',
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 003000; -- 00:30 AM
GO
EXEC sp_attach_schedule
@job_name = N'Daily Full Database Backup',
@schedule_name = N'Daily';
GO
EXEC sp_add_jobserver
@job_name = N'Daily Full Database Backup';
GOExample 4: How to create a file backup of a database of two files
The below sample script will create a differential file backup of only the DishFinanceFG1 file of the FinanceGroup1 filegroup and the DishFinanceFG2 file of the FinanceGroup2 filegroup.
--Backup the files in the DishFinanceFG1 secondary filegroup.
BACKUP DATABASE [DishTV]
FILE = 'DishFinanceFG1',
FILE = 'DishFinanceFG2'
TO DISK = 'K:\DBBackups\DishTV\DishTV-FY2022-2023.bak', STATS = 5, COMPRESSION;
GOExample 5: How to take database backup in Always-On environment
-- Specify the location of database backup folder to put the files.
DECLARE @DBBackupFolder VARCHAR(256) = 'E:\DBBackupFiles\'
-- Specify the name of user database to take the backup.
DECLARE @UserDBName VARCHAR(100) = 'Name Of Database'
DECLARE @DBBackupFileName VARCHAR(200)
DECLARE @CurrentDateTime VARCHAR(20)
-- To Get current date & Time for the database backup file
SET @CurrentDateTime = CONVERT(NVARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR, GETDATE(), 108), ':', '')
-- Now Create full backup file name & store in the specified folder
SET @DBBackupFileName = @DBBackupFolder + @UserDBName + '_Backup_' + @CurrentDateTime + '.bak'
-- Check whether the database is part of AG r not. If the database is part of AG,
-- perform the specified database backup
IF sys.fn_hadr_backup_is_preferred_replica(@UserDBName) = 1
BEGIN
BACKUP DATABASE @UserDBName TO DISK = @DBBackupFileName
WITH FORMAT,INIT,NAME = @UserDBName,SKIP, NOREWIND,NOUNLOAD,STATS = 5, COMPRESSION
END
ELSE
BEGIN
RAISERROR('This database is not part of always on.', 16, 1)
END
Conclusion
Effective database backup solutions are critical to ensuring data integrity and availability. Understanding the different types of backups, using T-SQL commands, and leveraging automation technologies such as SQL Server Agent and PowerShell will help you ensure robust data protection for SQL Server databases.
FAQs
Q: How do I create a backup in SQL Server?
A: Use the BACKUP T-SQL command.
Q: What is SQL Server database backup?
A: It is a replica of your database that can be utilized to recover data in the event of a failure.
Q: Why is it necessary to back up your SQL Server database?
A: To avoid data loss and ensure recovery in the event of corruption or calamity.
Q: What backup types are available in SQL Server?
A: Full, differential, and transaction log backups.
Q: How frequently should I back up my database?
A: It is determined by your data change pace and recovery objectives; full backups are usually performed weekly, with transaction log backups occurring every few hours.
Q: Can I automate SQL Server backups?
A: Yes, using SQL Server Agent or PowerShell scripts.
Q: What permissions are required for database backups?
A: Sysadmin or database backup operator role.
Q: Where should I keep my database backups?
A: In a secure and dependable storage facility, ideally offsite.
Q: What is a differential backup?
A backup that only includes changes since the last complete backup.
Q: What are the best SQL Server backup practices?
A: Perform and test backups regularly, making numerous copies and storing them securely.
Review the below articles also
Dbcc Freeproccache: A powerful command
PAGEIOLATCH_SH: Unveiling the Secrets
Discover Polybase: Top 7 Usage
Unlocking the Power of DBCC USEROPTIONS in SQL Server
SQL Server 2019: Powerful RDBMS