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

SQL Server Database & Recovery Model

SQL Server: Type of Databases & Recovery Model

Type of Databases in SQL Server
There are two type of databases in SQL Server:

1. System Databases: SQL Server includes following system databases:
- master Database:
Records all the system-level information for an instance of SQL Server.
- msdb Database:
Is used by SQL Server Agent for scheduling alerts and jobs-history and all.
- model Database:
Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
- Resource Database:
Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
- tempdb Database:
Is a workspace for holding temporary objects or intermediate result sets.
2. User Databases: This includes databases created by users for any application.
Following are the queries to check all databases and status details:

On SQL Server 2000:

SELECT * FROM master.dbo.sysdatabases

On SQL Server 2005 and above:

SELECT * FROM sys.databases 
OR
 Sp_Helpdb

Using SSMS, Here are the Database Details

Recovery Model of Databases
SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

1. Simple:
- No log backups.
- Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
Work loss exposure
- Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.
Point in time Recovery.
- Can recover only to the end of a backup.

2. Full:
- Requires log backups.
- No work is lost due to a lost or damaged data file.
Work loss exposure
- Normally none.
- If the tail of the log is damaged, changes since the most recent log backup must be redone.
Point in time Recovery.
- Can recover to a specific point in time, assuming that your backups are complete up to that point in time

3. Bulk Logged:
- Requires log backups.
- An adjunct of the full recovery model that permits high-performance bulk copy operations.
- Reduces log space usage by using minimal logging for most bulk operations.
Work loss exposure
- If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Otherwise, no work is lost.
Point in time Recovery.
- Can recover to the end of any backup. Point-in-time recovery is not supported.

T-SQL Command to check Recovery model for the databases

SELECT name, recovery_model_desc FROM sys.databases

To change the recovery model

USE master ;
ALTER DATABASE [Database_Name] SET RECOVERY FULL; GO

Using the SSMS to change the recovery model for the AdventureWorks database:

Follow Up Recommendations: After You Change the Recovery Model

-- After switching between the full and bulk-logged recovery models
- After completing the bulk operations, immediately switch back to full recovery mode.
- After switching from the bulk-logged recovery model back to the full recovery model, back up the log.
Note: Your backup strategy remains the same: continue performing periodic database, log, and differential backups.
-- After switching from the simple recovery model
- Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
Note: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
- Schedule regular log backups, and update your restore plan accordingly.
Important Note: If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
-- After switching to the simple recovery model
- Discontinue any scheduled jobs for backing up the transaction log.
- Ensure periodic database backups are scheduled.
- Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.
Recommended, for Critical Databases SET FULL Recovery model and schedule frequent t-log backup. So that we can recover data with minimal data loss.