Backup Details of SQL

Dear Readers,

In this article, we will see the following Backup Details of SQL.

SQL Server offers many options for creating backups. As we discussed what types of backups can be performed based on the recovery model of the database.
Here different types of backups that can be created in SQL Server.
• Full backups
• Differential backups
• File backups
• File group backups
• Partial backups
• Copy-Only backups
• Mirror backups
• Transaction log backups
SQL Server Full Backups
The most common types of SQL Server backups are complete or full backups, also known as database backups. These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.
A full backup can be completed either using T-SQL or by using SSMS. Here few examples how to create a full backup.
Create full SQL Server database backup to one disk file
T-SQL
This will create a full backup of the Adventure Works database and write the backup contents to file “C:\AdventureWorks.BAK”. The .BAK extension is commonly used for identifying that the backup is a full database backup.

SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select “Full” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” and click “OK”
• Click “OK” again to create the backup

SQL Server Transaction Log Backups
If your database is set to the “Full” or “Bulk-logged” recovery model then you will be able to is

Issue “Transaction Log” backups. By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred. The only thing to this is if your database is set to the “Bulk-logged” recovery model and a bulk operation was issued, you will need to restore the entire transaction log, so you cannot do a point in time restore using a transaction log backup that contains a bulk-logged operation.
After the transaction log backup is issued, the space within the transaction log can be reused for other processes. If a transaction log backup is not taken and the database is not using the Simple recovery model the transaction log will continue to grow.
A transaction log backup can be completed either using T-SQL or by using SSMS. Here few examples how to create a transaction log backup.
Create SQL Server Transaction Log Backup to one disk file
T-SQL
This will create a transaction log backup of the Adventure Works database and write the backup contents to file “C:\AdventureWorks.TRN”. The .TRN extension is commonly used for identifying that the backup is a transaction log backup.

SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select “Transaction Log” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.TRN” and click “OK”
• Click “OK” again to create the backup

SQL Server Differential Backups
A “Differential” backup is a backup of any extent that has changed since the last “Full” backup was created.
The way differential backups work is that they will back up all extents that have changed since the last full backup. An extent is made up of eight (8KB) pages, so an extent is (64KB) of data. Each time any data has been changed a flag is turned on to SQL Server know that if a Differential backup is created it should include the data from this extent. When a Full backup is taken these flags are turned off.
So if you do a full backup and then do a differential backup, the differential backup will contain only the extents that have changed. If you wait some time and do another differential backup, this new differential backup will contain all extents that have changed since the last full backup. Each time you create a new differential backup it will contain every extent changed since the last full backup. When you go to restore your database, to get to the most current time you only need to restore the full backup and the most recent differential backup. All of the other differential backups can be ignored.
If your database is in the Simple recovery model, you can still use full and differential backups. This does not allow you to do point in time recovery, but it will allow you to restore your data to a more current point in time then if you only had a full backup.
If your database is in the Full or Bulk-Logged recovery model you can also use differential backups to eliminate the number of transaction logs that will need to be restored. Since the differential will back up all extents since the last full backup, at restore time you can restore your full backup, your most recent differential backup and then any transaction log backups that were created after the most recent differential backup. This cuts down on the number of files that need to be restored.
Create SQL Server Differential Backup to one disk file
T-SQL

SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select “Differential” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.DIF” and click “OK”
• Click “OK” again to create the backup

SQL Server File Backups
This allows you to backup each file independently instead of having to back up the entire database. This is only relevant when you have created multiple data files for your database. One reason for this type of backup is if you have a very large file and need to back them up individually. For the most part you probably only have one data file, so this is option is not relevant.
As mentioned above you can back up each data file individually. If you have a very large database and have large data files this option may be relevant.
A file backup can be completed either using T-SQL or by using SSMS. Few examples to show how to create a transaction log backup.
Create a SQL Server file backup of the Test Backup database
For this example I created a new database called Test Backup that has two data files and one log file. The two data files are called ‘Test Backup’ and ‘TestBackup2’. The code below shows how to backup each file separately.
T-SQL

SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select either “Full” or “Differential” as the backup type
• Select “Files and file groups”
• Select the appropriate file and click “OK”

• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\TestBackup_TestBackup.FIL” and click “OK”
• Click “OK” again to create the backup and repeat for other files

SQL Server File group Backups
In addition to doing “File” backups you can also do “File group” backups which allow you to backup all files that are in a particular file group. By default each database has a PRIMARY file group which is tied to the one data file that is created. You have an option of creating additional file groups and then placing new data files in any of the file groups. The one advantage of using filegroup backups over file backups is that you can create a Read-Only filegroup which means the data will not change. So instead of backing up the entire database all of the time you can just backup the Read-Write file groups.
A filegroup backup can be completed either using T-SQL or by using SSMS.
Create a SQL Server filegroup backup of the Test Backup database
For this example I created a new database called Test Backup that has three data files and one log file. Two data files are the PRIMARY filegroup and one file is in the Read Only filegroup. The code below shows how to do a filegroup backup.
T-SQL

SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select either “Full” or “Differential” as the backup type
• Select “Files and file groups”
• Select the appropriate filegroup and click “OK”

• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\TestBackup_ReadOnly.FLG” and click “OK”
• Click “OK” again to create the backup and repeat for other filegroups

SQL Server Partial Backups
A new option is “Partial” backups which were introduced with SQL Server 2005. This allows you to back up the PRIMARY filegroup, all Read-Write file groups and any optionally specified files. This is a good option if you have Read-Only file groups in the database and do not want to back up the entire database all of the time.
A Partial backup can be issued for either a Full or Differential backup. This can not be used for Transaction Log backups. If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.
A partial backup can be completed only by using T-SQL. The following examples show you how to create a partial backup.
Create a SQL Server partial backup of the Test Backup database
For this example I created a new database called Test Backup that has three data files and one log file. Two data files are the PRIMARY filegroup and one file is in the Read Only filegroup. The code below shows how to do a partial backup.
T-SQL

Create a SQL Server differential partial backup

SQL Server Backup Commands
There are primarily two commands that are used to create SQL Server backups they are
• BACKUP DATABASE and
• BACKUP LOG
These commands also have various options that you can use to create full, differential, file, transaction log backups, etc. as well as other options to specify how the backup command should function and any other data to store with the backups.
SQL Server BACKUP DATABASE command
This allows you to do a complete backup of your database as well as differential, file, etc. backups depending on the options that you use.
The BACKUP DATABASE command gives you many options for creating backups. Some examples.
Create a full SQL Server backup to disk
The command is BACKUP DATABASE data baseName. The “TO DISK” option specifies that the backup should be written to disk and the location and filename to create the backup is specified.
B

Create a differential SQL Server backup
This command adds the “WITH DIFFERENTIAL” option.

Create a file level SQL Server backup
This command uses the “WITH FILE” option to specify a file backup. You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb ‘databaseName’, specifying the name of your database.

Create a filegroup SQL Server backup
This command uses the “WITH FILEGROUP” option to specify a filegroup backup. You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb ‘databaseName’, specifying the name of your database.

Create a full SQL Server backup to multiple disk files
This command uses the “DISK” option multiple times to write the backup to three equally sized smaller files instead of one large file.

Create a full SQL Server backup with a password
This command creates a backup with a password that will need to be supplied when restoring the database.

Create a full SQL Server backup with progress stats
This command creates a full backup and also displays the progress of the backup. The default is to show progress after every 10%.

Here is another option showing stats after every 1%.

Create a SQL Server backup and give it a description
This command uses the description option to give the backup a name. This can later be used with some of the restore commands to see what is contained with the backup. The maximum size is 255 characters.

Create a mirrored SQL Server backup
This option allows you to create multiple copies of the backups, preferably to different locations.

Specifying multiple options for SQL Server Backups
This next example shows how you can use multiple options at the same time.

SQL Server BACKUP LOG command
The primary is BACKUP DATABASE which backs up the entire database and BACKUP LOG which backs up the transaction log. The following will show different options for doing transaction log backups.
The BACKUP LOG command gives you many options for creating transaction log backups. Following are different examples.
Create a simple SQL Server transaction log backup to disk
The command is BACKUP LOG databaseName. The “TO DISK” option specifies that the backup should be written to disk and the location and filename to create the backup is specified. The file extension is “TRN”. This helps me know it is a transaction log backup, but it could be any extension you like. Also, the database has to be in the FULL or Bulk-Logged recovery model and at least one Full backup has to have occurred.

Create a SQL Server log backup with a password
This command creates a log backup with a password that will need to be supplied when restoring the database.

Create a SQL Server log backup with progress stats
This command creates a log backup and also displays the progress of the backup. The default is to show progress after every 10%.

Here is another option showing stats after every 1%.

Create a SQL Server backup and give it a description
This command uses the description option to give the backup a name. This can later be used with some of the restore commands to see what is contained with the backup. The maximum size is 255 characters.

Create a mirrored SQL Server Transaction Log backup
This option allows you to create multiple copies of the backups, preferably to different locations.

Specifying multiple options for SQL Server Transaction Log backup
This example shows how you can use multiple options at the same time.

How to create a SQL Server backup
Creating backups for SQL Server is very easy. There are a few things you need to consider:
How will you create the backups:
• T-SQL commands
• Using SQL Server Management Studio
• Creating maintenance plans and
• Using third party backup tools
What options will you use
• Backup to disk or to tape
• Types of backups; full, differential, log, etc…
The next two topics cover the basics on how to create backups using either T-SQL or SQL Server Management Studio.
Creating a backup using SQL Server Command Line (T-SQL)
Here are some simple examples on how to create database and log backups using T-SQL. This is the most basic syntax that is needed to create backups to disk.
Create a SQL Server full backup

Create a SQL Server transaction log backup

This is basically all you need to do to create the backups. There are other options that can be used, but to create a valid and useable backup file this is all that needs to be done.
Create backup using SQL Server Management Studio SSMS
Creating backups using SQL Server Management Studio is simple as well. Based on how simple the T-SQL commands are, there is a lot of clicking that needs to occur in SSMS to create a backup.
The following screen shots show you how to create a full backup and a transaction log backup.
• Expand the “Databases” tree
• Right click on the database name you want to backup
• Select “Tasks” then “Back Up…” as shown below

• Specify the “Backup type”; Full, Differential or Transaction Log

• Click on “Add…” to add the location and the name of the backup file
• Click “OK” to close this screen

• And click “OK” again to create the backup

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts