Database Backup

Database backup is also performed and managed to ensure that an organization complies with business and governance. A database backup is a copy of data from the database, which can be used to reconstruct the data. A database backup is a process to protect the data from various disasters. As you know disasters cannot be completely prevented; at least we can do is to ensure that we have backup is ready so that the service can be restored ASAP.

Possibility of data been vulnerable:

  • Corruption
  • Hardware failure including media failure, software failure, and OS errors
  • Attacks on the infra/network or other malicious actions
  • Manual errors or other inadvertent actions
  • Unauthorized changes
  • Undesirable events

Frequency of backups

The frequency to run your backups depends on your business need. Following are the suggestion that you may consider when plan for backup schedule:

  • Business critical data that changes frequently i.e. Production database – It is recommended to take backup daily and hourly backups.
  • Data that changes every few days – Weekly or Monthly backup can be scheduled.
  • For some data, when you make changes – On demand backup can be executed.

Backup destination

Database backups can be stored in the following locations:

  • Local storage – is used if you want to save the backup on a removable storage device attached to the computer or on a local computer drive.
  • Shared folder – is used if you want to save the backup in a network shared folder
  • Tape devices – are used in case robust archival is required.
  • Cloud backup
    1. Public cloud storage – this type of storage is often used to backup the data as part of the Disaster Recovery where users send the data over to public cloud services such as S3. The providers generally charge based on monthly usage of storage. Some providers also charge for data transfers and access.
    2. Private cloud storage – is referred to as internal cloud storage. In this case, the data is backed up to servers within the environment, based on pre-determined set of policies and company’s firewall.
    3. Hybrid cloud storage – is a combination of public and private cloud.

Types of Backups

SQL Server supports different types of backups for databases. However, selection of backup type depends on the database recovery model.

  • Full Backup
  • Differential Backup
  • Transaction Log Backup
  • File Backup
  • FileGroup Backup
  • Partial Backup
  • Copy-Only Backup
  • Mirror Backup

Backup on-Premises

Full Backup

Using SQL Server Management Studio

STEP 1: After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
STEP 2: Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up.

STEP 3: On the General page in the Destination section select Disk from the Back up to: drop-down list.
STEP 4: Select Remove until all existing backup files have been removed.
STEP 5: Select Add and the Select Backup Destination dialog box will open.
STEP 6: Enter a valid path and file name in the File name text box and use .bak as the extension to simplify the classification of this file.
STEP 7: Click OK and then click OK again to initiate the backup.
STEP 8: When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.

Create an encrypted backup

The SQLTestDB database will be backed up with encryption to the default backup location.

STEP 1: After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
STEP 2: Expand Databases, expand System Databases, right-click master, and click New Query to open a query window with a connection to your SQLTestDB database.
STEP 3: Execute the following commands to create a database master key and a certificate within the master database.

-- Create the master key 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
-- If the master key already exists, open it in the same session that you create the certificate (see next step)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
-- Create the certificate encrypted by the master key
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

STEP 4: In Object Explorer, in the Databases node, right-click SQLTestDB, point to Tasks, and then click Back Up.
STEP 5: On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.
STEP 6: On the Backup Options page in the Encryption section select the Encrypt backup check box.
STEP 7: From the Algorithm drop-down list, select AES 256.
STEP 8: From the Certificate or Asymmetric key drop-down list select MyCertificate.
STEP 9: Select OK.

Back up to the Azure Blob storage service

A full database backup of SQLTestDB to the Azure Blob storage service.

Note: If you do not have an Azure blob container in a storage account, create one before continuing.

STEP 1: After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
STEP 2: Expand Databases, right-click SQLTestDB, point to Tasks, and then click Back Up.
STEP 3: On the General page in the Destination section select URL from the Back up to: drop-down list.
STEP 4: Click Add and the Select Backup Destination dialog box will open.
STEP 5: If you have previously registered the Azure storage container that you wish to use with SQL Server Management Studio, select it. Otherwise, click New container to register a new container.
STEP 6: In the Connect to a Microsoft Subscription dialog box, sign in to your account.
STEP 7: In the Select Storage Account drop-down text box, select your storage account.
STEP 8: In the Select Blob Container drop-down text box, select your blob container.
STEP 9: In the Shared Access Policy Expiration drop-down calendar box, select an expiration date for the shared access policy that you create in this example.
STEP 10: Click Create Credential to generate a shared access signature and credential in SQL Server Management Studio.
STEP 11: Click OK close the Connect to a Microsoft Subscription dialog box.
STEP 12: In the Backup File text box, modify the name of the backup file (optional).
STEP 13: Click OK to close the Select a backup destination dialog box.
STEP 14: Click OK to initiate the backup.
STEP 15: When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.

Using Transact-SQL

Create a full database backup by executing the BACKUP DATABASE statement to create the full database backup, specifying:

  • The name of the database to back up.
  • The backup device where the full database backup is written.
Syntax: 
BACKUP DATABASE database TO backup_device [ ,...n ]
[ WITH with_options [ ,...o ] ] ;
Back up to a disk device

The following example backs up the complete SQLTestDB database to disk, by using FORMAT to create a new media set.

USE SQLTestDB; 
GO
BACKUP DATABASE SQLTestDB TO DISK = 'c:\tmp\SQLTestDB.bak'
WITH FORMAT,
MEDIANAME = 'SQLServerBackups',
NAME = 'Full Backup of SQLTestDB';
GO
Back up to a tape device

The following example backs up the complete SQLTestDB database to tape, appending the backup to the previous backups.

USE SQLTestDB; 
GO
BACKUP DATABASE SQLTestDB
TO TAPE = '\\.\Tape0'
WITH NOINIT,
NAME = 'Full Backup of SQLTestDB';
GO
Back up to a logical tape device

The following example creates a logical backup device for a tape drive. The example then backs up the complete SQLTestDB database to that device.

-- Create a logical backup device, 
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0';
USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB TO SQLTestDB_Bak_Tape
WITH FORMAT,
MEDIANAME = 'SQLTestDB_Bak_Tape',
MEDIADESCRIPTION = '\\.\tape0',
NAME = 'Full Backup of SQLTestDB';
GO

Differential Backup

Using SQL Server Management Studio

STEP 1: After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
STEP 2: Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.
STEP 3: Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
STEP 4: In the Database list box, verify the database name. You can optionally select a different database from the list.
You can perform a differential backup for any recovery model (full, bulk-logged, or simple).
STEP 5: In the Backup type list box, select Differential.
Important: When you select Differential, verify that the Copy Only Backup check box is cleared.
STEP 6: For Backup component, click Database.
STEP 7: Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
STEP 8: Optionally, in the Description text box, enter a description of the backup set.
STEP 9: Specify when the backup set will expire:

  • To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; 0 days means the backup set will never expire.
  • The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
  • To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

STEP 10: Choose the type of backup destination by clicking Disk or Tape. To select the path of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.
STEP 11: To view or select the advanced options, click Options in the Select a page pane.
STEP 12: Select an Overwrite Media option, by clicking one of the following:

  • Back up to the existing media set
  • Back up to a new media set, and erase all existing backup sets

STEP 13: In the Reliability section, optionally, check:

  • Verify backup when finished.
  • Perform checksum before writing to media.

Transact-SQL

Execute the BACKUP DATABASE statement to create the differential database backup, specifying:

  • The name of the database to back up.
  • The backup device where the full database backup is written.
  • The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last full database backup was created are backed up.
Syntax:
BACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL
Example: 
-- Create a full database backup first.
BACKUP DATABASE MyAdvWorks
TO MyAdvWorks_1
WITH INIT;
GO
-- Time elapses.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE MyAdvWorks
TO MyAdvWorks_1
WITH DIFFERENTIAL;
GO

Transaction Log Backup

Using SQL Server Management Studio

STEP 1: After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
STEP 2: Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
STEP 3: Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
STEP 4: In the Database list box, verify the database name. You can optionally select a different database from the list.
STEP 5: Verify that the recovery model is either FULL or BULK_LOGGED.
STEP 6: In the Backup type list box, select Transaction Log.
STEP 7: (optional) Select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups, see Copy-Only Backups (SQL Server).
Note: When the Differential option is selected, you cannot create a copy-only backup.
STEP 8: Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
STEP 9: (optional) In the Description text box, enter a description of the backup set.
STEP 10: Specify when the backup set will expire:

  • To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
  • The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this dialog box, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
  • To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

STEP 11: Choose the type of backup destination by clicking Disk, URL or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.
To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

STEP 12: To view or select the advanced options, click Options in the Select a page pane.

STEP 13: Select an Overwrite Media option, by clicking one of the following:

  • Back up to the existing media set
  • For this option, click either Append to the existing backup set or Overwrite all existing backup sets.
    • (optional) Select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
    • (optional) Enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
    • If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.
  • Back up to a new media set, and erase all existing backup sets
    • For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

STEP 14: In the Reliability section, optionally, check:

  • Verify backup when finished.
  • Perform checksum before writing to media and (optional) Continue on checksum error.

STEP 15: In the Transaction log section:

  • For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
  • To back up the tail of the log (the active log), check Back up the tail of the log, and leave database in the restoring state.
  • A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL.

STEP 16: If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

Using Transact-SQL

Execute the BACKUP LOG statement to back up the transaction log, specifying the following:

  • The name of the database to which the transaction log that you want to back up belongs.
  • The backup device where the transaction log backup is written.
--This example creates a transaction log backup for the AdventureWorks2012 --database to the previously created named backup device, 
--MyAdvWorks_FullRM_log1.
BACKUP LOG AdventureWorks2012 TO MyAdvWorks_FullRM_log1;
GO

Files and Filegroups Backup

Using SQL Server Management Studio

STEP 1: After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
STEP 2: Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
STEP 3: Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
STEP 4: In the Database list, verify the database name. You can optionally select a different database from the list.
STEP 5: In the Backup type list, select Full or Differential.
STEP 6: For the Backup component option, click File and Filegroups.
STEP 7: In the Select Files and Filegroups dialog box, select the files and filegroups you want to back up. You can select one or more individual files or check the box for a filegroup to automatically select all the files in that filegroup.
STEP 8: Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
STEP 9: (optional) In the Description text box, enter a description of the backup set.

STEP 10: Specify when the backup set will expire:

To have the backup set expire after a specific number of days, click After (the default option) and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this option, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

STEP 11: Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives that contain a single media set, click Add. The selected paths are displayed in the Backup to list.
STEP 12: To view or select the advanced options, click Options in the Select a page pane.

STEP 13: Select an Overwrite Media option, by clicking one of the following:

  • Back up to the existing media set
  1. (optional) Select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
  2. (optional) Enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name that you enter here.
  • Back up to a new media set, and erase all existing backup sets

STEP 14: (optional) In the Reliability section, check:

  • Verify backup when finished.
  • Perform checksum before writing to media, and (optional) Continue on checksum error.

Using Transact-SQL

To create a file or filegroup backup, use a BACKUP DATABASE statement. Minimally, this statement must specify the following:

  • The database name.
  • A FILE or FILEGROUP clause for each file or filegroup, respectively.
  • The backup device on which the full backup will be written.
Syntax:
BACKUP DATABASE database { FILE =logical_file_name | FILEGROUP =logical_filegroup_name }
[ ,…f ] TO backup_device [ ,…n ]
[ WITH with_options [ ,…o ] ] ;

Example
The following examples back up one or more files of the secondary filegroups of the Sales database. This database uses the full recovery model and contains the following secondary filegroups:

  • A filegroup named SalesGroup1 that has the files SGrp1Fi1 and SGrp1Fi2.
  • A filegroup named SalesGroup2 that has the files SGrp2Fi1 and SGrp2Fi2.

Create a file backup of two files

The following example creates a differential file backup of only the SGrp1Fi2 file of the SalesGroup1 and the SGrp2Fi2 file of the SalesGroup2 filegroup.

--Backup the files in the SalesGroup1 secondary filegroup.   
BACKUP DATABASE Sales
FILE = 'SGrp1Fi2',
FILE = 'SGrp2Fi2'
TO DISK = 'G:\SQL Server Backups\Sales\SalesGroup1.bck';
GO

Create a full file backup of the secondary filegroups

The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.   
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck';
GO

Create a differential file backup of the secondary filegroups

The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1.   
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'C:\MySQLServer\Backups\Sales\SalesFiles.bck'
WITH
DIFFERENTIAL;
GO

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply