SQL Server Backup & Restore

Recovery Point Objective (RPO) and Recovey Time Objective (RTO) are some of the most important parameters of a disaster recovery or data protection plan. These objectives guide the enterprises in choosing an optimal data backup (or rather restore) plan.

RPO – Recovery Point Objective

    Recovery Point Objective (RPO) describes the amount of data lost – measured in time. Example: After an outage, if the last available backup of data was from 10 hours ago, then the RPO would be 10 hours.
In other words it is the answer to the question – “Up to what point in time can the data be recovered?“.

RTO – Recovery Time Objectives
    The Recovery Time Objective (RTO) is the duration of time and a service level within which a business process must be restored after a disaster in order to avoid unacceptable consequences associated with a break in continuity.
In another words it is the answer to the question – “How much time did you take to recover after notification of a business process disruption ?“

Backup & RestoreBackup:

A copy of SQL Server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log.

                                           Types of Backups

  Full backups: Full backups create a complete backup of your database  as well as part of the transaction log, so the database can be recovered.
Backups can be taken into multiple files which is known as split backup.
Split backup is helpful, if the backup size is huge and cannot be taken into one drive.
Here are the commands to take Full backup of the database: T-SQL

--Command to take backup into one backup file
BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\BackupFile\Full\AdventureWorks.BAK'
GO
 
--Command to take backup into multiple backup file
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\BackupFile\Full\AdventureWorks_1.BAK',
TO DISK = 'C:\BackupFile\Full\AdventureWorks_2.BAK'
GO
--Command to take backup into multiple backup file to multiple disk
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\BackupFile\Full\AdventureWorks_1.BAK',
TO DISK = 'D:\BackupFile\Full\AdventureWorks_2.BAK' 
GO

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" an click "OK"
• Click "OK" again to create the backup

 Differential backups: A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data extents that have changed since the differential base. A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.

Here are the few conditions according to recovery model:

1. 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.
2. 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 backup 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.

Diff backup can also be taken as split backup.
Here are the commands to take Differential backup of the database T-SQL

--Command to take backup into one backup file
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\BackupFile\Diff\AdventureWorks.BAK'
WITH  DIFFERENTIAL
GO
--Command to take backup into multiple backup file
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\BackupFile\Diff\AdventureWorks_1.BAK',
TO DISK = 'C:\BackupFile\Diff\AdventureWorks_2.BAK'
WITH  DIFFERENTIAL 
GO

--Command to take backup into multiple backup file to multiple disk
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\BackupFile\Diff\AdventureWorks_1.BAK',
TO DISK = 'D:\BackupFile\Diff\AdventureWorks_2.BAK' 
WITH  DIFFERENTIAL
GO

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:\Backup\Diff\AdventureWorks.Bak" and click "OK"
• Click "OK" again to create the backup

Transaction log Backups (T-log):
A transaction log backup allows you to backup the active part of the transaction log.  So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed.  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, the transaction log will continue to grow.
Here are the few conditions according to recovery model:

1. If your database is in the Simple recovery model, you cannot take the T-log backup. 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.
2. If your database is in the Full or Bulk-Logged recovery model you can also use T-log backups to avoid data loss for mission critical database, 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.

Here is the command to take transaction log backup of the database T-SQL

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backup\Log\AdventureWorks.TRN'
GO

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

File backups:         Another option for backing up your databases is to use "File" backups.  This allows you to backup each file independently instead of having to backup 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 files and need to back them up individually.
Command to take a file backup of the database.
For this example, TestDB Database that has 3 data files and one log file.  The 3 data files are called 'TestDB', 'TestDB1' and 'TestDB2'.  The code below shows how to backup each file separately.
T-SQL
BACKUP DATABASE TestDB FILE = 'TestDB' TO DISK = 'C:\BackupFile\FileBckup\TestDB_FileBackup.Bak' GO BACKUP DATABASE TestDB FILE = 'TestDB1' TO DISK = 'C:\BackupFile\FileBckup\TestDB1_FileBackup.Bak' GO

BACKUP DATABASE TestDB FILE = 'TestDB2' 
TO DISK = 'C:\BackupFile\FileBckup\TestDB2_FileBackup.Bak'
GO 

Filegroup backups:
      A "Filegroup" backups which allows you to backup all files that are in a particular filegroup.  By default each database has a PRIMARY filegroup which is tied to the one data file that is created.  You have an option of creating additional filegroups and then placing new data files in any of the filegroups.    Partial backups.Command to take a filegroup backup of the database
For this example, TestDB Database that has 3  data files and one log file.  2 data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup.  The code below shows how to do a filegroup backup.
T-SQL
BACKUP DATABASE TestDB FILEGROUP = 'ReadOnly' TO DISK = 'C:\BackupFile\FGBackup\TestDB_ReadOnly.BAK' GO

Partial Backups:This backup allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files.  This is a good option if you have Read-Only filegroups in the database and do not want to backup 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.

Command to take a partial backup of the TestDB database
For this example, TestDB Database that has three data files and one log file.  Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup.  The code below shows how to do a partial backup.
T-SQL to take a full partial backup

BACKUP DATABASE TestDB READ_WRITE_FILEGROUPS
TO DISK = 'C:\TestDB_Partial.BAK'
GO

T-SQL to take a differential partial backup

BACKUP DATABASE TestDB READ_WRITE_FILEGROUPS
TO DISK = 'C:\TestDB_Partial.BAK'
WITH DIFFERENTIAL
GO

Copy-Only backups:

          A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.
The types of copy-only backups are as follows:

• Copy-only full backups (all recovery models) A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base. Restoring a copy-only full backup is the same as restoring any other full backup.

Copy-only log backups (full recovery model and bulk-logged recovery model only) A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore. The transaction log is never truncated after a copy-only backup.

Here is the command to take copy only backup:
--For Copy only Full Backup
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\BackupFile\Full\AdventureWorks.BAK'
COPY_ONLY
GO
--For Copy only log Backup
BACKUP Log AdventureWorks
TO DISK = 'C:\BackupFile\Full\AdventureWorks.TRN'
COPY_ONLY
GO
Note: ".BAK" is the default extension of all the backups and ".TRN" is the extension used for transaction log backup. 

Restore:

Now the way to restore above backups, but make sure when restoring a database will need exclusive access to the database, which means no other user connections can be using the database.

                                                          Types of Restore

Restore a full backup: This will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup.

RESTORE DATABASE AdventureWorks 

FROM DISK = 'C:\Backupfile\Full\AdventureWorks.BAK'
GO

Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.

RESTORE DATABASE AdventureWorks 
FROM DISK ='C:\Backupfile\Full\AdventureWorks.BAK'
WITH NORECOVERY
GO

Restore a differential backup: To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

--Command to restore Full backup first
RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backupfile\Full\AdventureWorks.BAK' 
WITH NORECOVERY
GO

--Command to restore Diff backup 
RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backupfile\Diff\AdventureWorks.BAK'
With RECOVERY
GO

Restore a transaction log backup: To restore a transaction log backup the database need to be in a restoring state.  This means that you would have to restore a full backup and possibly a differential backup as well.

RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backupfile\Log\AdventureWorks.TRN'
GO

Point in time restore:

Point in time restore is possible only in FULL recovery model.
Here are steps to restore backups:

1. Take a tail log backup of the database which needs to be restored
2.Restore the most recent full backup with the NORECOVERY clause.

--Command to restore Full backup first WITH NORECOVERY
RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backupfile\Full\AdventureWorks.BAK' 
WITH NORECOVERY
GO

3. Restore the most recent differential backup with the NORECOVERY clause

--Command to restore Diff backup WITH NORECOVERY
RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backupfile\Diff\AdventureWorks.BAK'
GO

4. Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup.

--Command to restore all log backup till the second last WITH NORECOVERY

RESTORE LOG AdventureWorks 
FROM DISK = 'C:\Backupfile\Log\AdventureWorks_1.TRN' 
WITH NORECOVERY
GO

5.Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied.

--Command to restore last log backup with RECOVERY
RESTORE LOG AdventureWorks 
FROM DISK = 'C:\Backupfile\Log\AdventureWorks_2.TRN'
WITH RECOVERY 
GO