So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore database command gives an option to test the consistency and integrity of the backup file.
As we all know, the backup database command bound with many database options. Indeed, it facilitates the execution of specific backup database command that meets the business requirement.
The backup database is further classified into two preliminary backup types
- Backup Database
- Backup Log
Each of the above types defines the type of data.
Let’s deep dive and review each of the database backup command topics to get a better understanding of what it is all about. In this article, we will find an answer for FAQs about the SQL Server database backup. We will learn more about database backup.
- Why are database backups so important?
- What is a SQL Server database backup?
- What are different types of database backups?
- What is a database recovery model?
- How can I check the recovery model of the database?
- How can I change the recovery model?
- What is a full database backup?
- How can I create a compressed backup on a newly formatted backup file?
- How can I overwrite to an existing backup file?
- How can I append backup sets in SQL Server database?
- How can I get Files and Filegroup information in SQL Server?
- What are system stored procedures that provide database information?
- What is a Differential backup?
- What are T-Log backups and How can I create T-log (Transaction-log) backups?
- What is a Tail-log backup?
- What is a Copy-only backup?
- What is a mirror backup?
- What is a Partial database backup?
- What is a Striped (Split) database backup in SQL Server?
- What is a file and filegroup backup?
- How can I set expiration dates for backups?
- How can I retain backup for a specific number of days?
- How can I encrypt a database backup using certificate?
- What is the difference between FULL, Bulk-Logged and Simple recovery models?
- What are system databases?
1. Why are database backups so important?
They is so important because of following reasons:
- Data is always the target and remain vulnerable for various threats
- Un-reliable on hardware and software programs
- Importance of the data
- Critical downtime
- It all depends on the amount of time it takes to redo the work. If it’s minor; then it can be ignored. If this is a major data loss, it may take several business days and it could feasibly end up in a heavy loss for the organization.
2. What is a SQL Server database backup?
A Backup is a process to create a copy of data and it can be used to reconstruct the data in case of any failures.
Backing up your SQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, on-site, off-site, and cloud location protects and safeguard the data from potentially catastrophic or various types of data loss.
3. What are different types of database backups?
The following are the different types of SQL Server database backups.
- Full
- Differential
- Transactional Log(T-Log)
- Copy-Only
- File
- FileGroup
- Partial
- Mirror
The type of a database backup depends on the database recovery model of the database.
4. What is a database recovery model?
The Recovery Model is a property of a database that controls how the transactions are logged.
The design of entire database and recovery procedures based on the context of the recovery model of the database.
It controls and manages each transaction scope into its entirety. You can refer to it here
5. How can I check the recovery model of the database?
In the following example, query the sys.databases catalog view the recovery model of the all the databases
|
1 |
SELECT name, recovery_model_desc FROM sys.databases; |
6. How can I change the recovery model?
The following example, the recovery model of the model database is set to FULL using the SET RECOVERY option of the ALTER DATABASE statement.
|
1 |
ALTER DATABASE model SET RECOVERY FULL |
What permission is required to take a database backup?
By default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
7. What is a full database backup?
In this backup type, the whole database is backed up. This is the base for any type of backups. In order to have further differential or transaction log backups, you must create the full database backup.
|
1 |
BACKUP DATABASE PowerSQL TO DISK='f:\PowerSQL\PowerSQL_FULL.BAK' |
How can I find the progress of percentage completion of the database backup?
Use the keyword STATS in the T-SQL to monitor backup progress status. This can also be used with restore command to measure the progress.
|
1 |
BACKUP DATABASE PowerSQL TO DISK='f:\PowerSQL\PowerSQL_FULL.BAK' WITH STATS |
8. How can I create a compressed backup on a newly formatted backup file?
Use the keyword FORMAT and COMPRESSION to format and compress the database backup.
|
1 2 |
BACKUP DATABASE PowerSQL TO DISK='f:\PowerSQL\PowerSQL_FULL.BAK' WITH STATS,FORMAT, COMPRESSION; |
9. How can I overwrite to an existing backup file?
On specifying the INIT keyword, all the backup sets are overwritten.
|
1 2 |
BACKUP DATABASE PowerSQL TO DISK='f:\PowerSQL\PowerSQL_FULL.BAK' WITH STATS,INIT |
10. How can I append backup sets in SQL Server database?
By default, the NOINIT option is enabled. It means that the backup will append to other backups in the file
|
1 2 |
BACKUP DATABASE PowerSQL TO DISK='f:\PowerSQL\PowerSQL_FULL.BAK' WITH STATS, NOINIT |
OR
|
1 |
BACKUP DATABASE PowerSQL TO DISK='f:\PowerSQL\PowerSQL_FULL.BAK' |
11. How can I get Files and Filegroup information in SQL Server?
You can query the sys,filegroups joined with sys. databases_files to get the filegroup related information.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT dbf.name AS filename, dbf.size/128 AS FileSizeMB, dfg.name AS FGName, dfg.type_desc, dbf.physical_name AS Physicalpath FROM sys.database_files AS dbf INNER JOIN sys.filegroups AS dfg ON dbf.data_space_id = dfg.data_space_id |
12. What are system stored procedures that provide database information?
The following the system stored procedures:
- sp_helpdb
- sp_helpfile
- sp_helpfilegroup
sp_helpdb ‘PowerSQL’
On specifying the database name, it displays the database information along with the list all the files and related filegroup of the database
sp_helpfile
sp_helpfile is a subset of sp_helpdb and it returns files, filegroup, and their properties.
sp_helpfile PowerSQL_1
On specifying the parameter, the file, it will list the details of that specified file and associated details.
sp_helpfilegroup
sp_helpfilegroup lists all the filegroups and number of associated data files in each filegroup.
On specifying the filegroup, the output lists the specific filegroup and associated data file details.
13. What is a Differential backup?
A Differential backup is also a type of SQL Server database backup where it copies all the data that has changed since the last full backup.
|
1 |
BACKUP DATABASE PowerSQL TO DISK = N'f:\PowerSQL\PowerSQL_Diff.BAK' WITH DIFFERENTIAL |
14. What are T-Log backups and How can I create T-log (Transaction-log) backups?
The transaction log records every modification made to the databases. The records are maintained in such a way that the system can be brought to a consistent state at any point of time with minimal or no data loss.
For T-Log backup, the full backup is the base. It takes complete log file data to write to a backup file.
The transaction log backup depends on the database recovery model and it’s relevant for the databases that are using the full or bulk-logged recovery models.
To execute the BACKUP LOG statement to back up the transaction log, specify the database name and backup device
|
1 |
BACKUP LOG PowerSQL TO DISK=N'f:\PowerSQL\PowerSQL_tlog.trc' |
How can I continue the database backup processes despite they encounter an error?
It’s basically overriding the default behavior of database backup process using CONTINUE_AFTER_ERROR. On error, the backup process will stop the process.
|
1 |
BACKUP DATABASE PowerSQL TO DISK = N'f:\PowerSQL\PowerSQL_Diff.BAK' WITH CHECKSUM, CONTINUE_AFTER_ERROR,STATS, FORMAT, COMPRESSION |
15. What is a Tail-log backup?
In case of failure, it is required to start the recovery process, the first and foremost important step is intended to ensure take care of tail part of the transaction before starting the restoration process is called tail-log backup.
WITH CONTINUE_AFTER_ERROR keyword, the SQL Server will override the default behavior even though it’s generating an error to complete the backup process.
|
1 2 3 |
USE MASTER GO BACKUP LOG PowerSQL TO DISK=N'f:\PowerSQL\PowerSQL_tlog.trc' WITH CHECKSUM, CONTINUE_AFTER_ERROR,STATS |
16. What is a Copy-only backup?
It’s a special type of backup. It is independent of the conventional backups and it will not have an impact on the overall backup process.
In simple words, it is used to create a full database or transaction log backup without breaking the log chain
|
1 |
BACKUP DATABASE PowerSQL TO DISK = N'f:\PowerSQL\PowerSQL_Diff.BAK' WITH COPY_ONLY,STATS, FORMAT, COMPRESSION |
17. What is a mirror backup?
In some cases, it is required to create multiple copies of data into different files. You can create a maximum of three mirror copies of the data at a time.
|
1 2 3 4 5 6 |
BACKUP DATABASE PowerSQL TO DISK = 'F:\PowerSQL\PowerSQL.BAK' MIRROR TO DISK = 'F:\PowerSQL\PowerSQL_1.BAK' MIRROR TO DISK = 'F:\PowerSQL\PowerSQL_2.BAK' WITH FORMAT, COMPRESSION, STATs GO |
18. What is a Partial database backup?
Partial database backup is one of the rarely used backup methods. All though it works with all recovery models, it is basically designed for simple database recovery model database. This provides flexibility for backing up only READ_WRITE_FILEGROUPS.
|
1 |
BACKUP DATABASE PowerSQL READ_WRITE_FILEGROUPS TO DISK = N'f:\PowerSQL\PowerSQL_Diff.BAK' WITH COPY_ONLY,STATS, FORMAT, COMPRESSION |
19. What is a Striped (Split) database backup in SQL Server?
This type of backup is mainly used where there is an issue with storage space.
In this type of database backup, the data will be split into parts and can be very useful during space constraints. Striped backup is a process of taking backup to different locations.
|
1 2 3 4 5 |
BACKUP DATABASE PowerSQL TO DISK = 'F:\PowerSQL\PowerSQL.BAK', DISK = 'F:\PowerSQL\PowerSQL_1.BAK', DISK = 'F:\PowerSQL\PowerSQL_2.BAK' WITH FORMAT, COMPRESSION, STATS=10 |
20. What is a file and filegroup backup?
Every SQL Server database must have a minimum of a data file and a log file. We can also create more than one files and it can be grouped together in filegroups for easier file management and administration purpose.
Using this method, the desired file\file group backup is possible
File Backup
Syntax: BACKUP DATABASE [DBNAME] FILE = N’FILENAME’ TO DISK = N’Path’
|
1 |
BACKUP DATABASE PowerSQL FILE = N'PowerSQL_Index' TO DISK = N'F:\PowerSQL\PowerSQL_Index.BAK' |
File Group Backup:
Syntax: BACKUP DATABASE [DBNAME] Filegroup = N’Filegroup Name’ TO DISK = N’Path’
|
1 2 3 4 |
BACKUP DATABASE PowerSQL Filegroup = N'PRIMARY', Filegroup = N'SalesInMemFG' TO DISK = N'F:\PowerSQL\PowerSQL_PRIMARYFG.BAK' WITH FORMAT, STATS |
21. How can I set expiration dates for backups?
If you want the backup to expire, use WITH EXPIREDATE clause option in the backup database T-SQL. The following example shows How can I back up with an expiration date on Jun 28, 2018:
|
1 |
BACKUP DATABASE PowerSQL TO DISK = N'F:\PowerSQL\PowerSQL_PRIMARYFG.BAK' WITH EXPIREDATE = N'08/28/2018 00:00:00' |
22. How can I retain backup for a specific number of days?
If you want to retain the backup for the only specific number of days then use the WITH RETAINDAYS clause with the database backup command.
|
1 |
BACKUP DATABASE PowerSQL TO DISK = N'F:\PowerSQL\PowerSQL.BAK' WITH RETAINDAYS = 3 , FORMAT, STATS=10 |
23. How can I encrypt a database backup using certificate?
- Create master Key
- Create certificate B
- Backup the database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE MASTER GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PowerSQL$2018' CREATE CERTIFICATE BackupServerCert WITH SUBJECT = 'Backup certificate' BACKUP DATABASE PowerSQL TO DISK = N'F:\PowerSQL\PowerSQL.BAK' WITH FORMAT, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = BackupServerCert ), STATS = 10 GO |
See Also,
Understanding Database Backup Encryption in SQL Server
24. What is the difference between FULL, Bulk-Logged and Simple recovery models?
| FULL | BULK LOGGED | SIMPLE | ||
| Log level | All | All, but minimal log for few operations such as bulk operations(bcp, BULK INSERT) Select into, Create index, alter index, drop index, updatetext, and writetext | Minimal | |
| Log Truncation Process (LTP) Life | During backup process | During backup process | Every time Checkpoint background run against the database | |
| Can it use for Production Server? | Yes | Depends – Yes/No | Depends – Yes/No | |
| Point-in-time recovery | Yes | No | No | |
| Log backup support | Yes | Yes | No | |
| Piecemeal Restore | Yes | Yes | Yes | |
| Log Shipping Support | Yes | Yes | No | |
| Database Mirroring Support | Yes | No | No | |
| Database Replication Support | Yes | Yes | Yes | |
25. What are system databases?
System databases are an essential component of SQL Server engine for the functioning of a server instance. The system databases are critical as it stores meta-data of the user-defined databases. It must be backed up after every significant update as we do it for user-defined databases. The system databases that you must always back up include msdb, master, model and configuration databases.
| System database | Description | Backup? | Recovery model |
| master | The database is used to record all of the system level information | Yes | Simple |
| model | It’s a template for all databases. | Yes | User configurable* |
| msdb | It is used by SQL Server Agent for job management and it also stores a history of every backup and restore operations | Yes | Simple (default) |
| Resource | A read-only database that contains copies of all system objects that ship with SQL Server | No | — |
| tempdb | A workspace for SQL Server | No | Simple |
| Configure Distribution | Configuration database for replication | Yes | Simple |
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021