Backup Levels

Description

Full

Entire database backup, including all filegroups or files in the database.

Logs only backup

A logs only backup, corresponds to a SQL   Server transaction log backup. A log file backup by itself cannot be used to   recover a database. A log file backup is used after a database recovery to   restore the database to the point of the original failure.

Note: The logs only backup option appears in   NMC. When using the Command

Line to perform logs only backup, use the txnlogs  command.

Cumulative incremental

Cumulative incremental captures all changes   since the last full backup.

Backup level

Advantages

Disadvantages

Full

Fastest restore time.

• Slow backup.
• Increases load on client, server, and network.
• Uses the most volume space.

Logs only

• Faster than a full backup.
• Decreases the load on server and  Uses the least volume   space.
• Enables point-in-time restore.

• Slow restore.
• Data can spread across multiple volumes.
• Multiple transaction logs can spread across multiple   volumes.

Cumulative incremental

• Faster than a full backup.
• Captures all changes since last full.

Generally more time-consuming than a logs-only backup (depending on the backup schedule strategy).

Type of recovery

When used

Description

Traditional recovery

For data that was backed up by traditional backup, NMM supports traditional recovery.

Data recovery from a traditional backup can be

performed:

• At any time by using NMM.
• By running NMM recover command (nsrsqlrc) from the command  prompt.

Traditional recovery operations recover files, file groups,   databases, and transaction log backups.

Normal recovery

NMM uses the normal restore type as the default.

The normal restore type restores:

• The entire set of data that is associated with one or more   SQL Server backups, including full, logs only, and cumulative incremental   backups.
• A file, file group, or a database to the database originally   backed up.
• Level full, level 1 (cumulative incremental), and level logs   only backups in the order required by SQL Server.

NMM can back up and restore specified files and file groups.   Also, a single file group, or multiple file groups or files, can be restored   from a full database backup.

Copy recovery

A copy recovery is an operation in which data is recovered   to a SQL Server host other than the one from which it was backed up. Note that   a copy restore from and to the same SQL Server instance can also be done.

The copy recovery type creates a copy of a database by restoring   a SQL Server database to a new location, or to a new database name. The copy   recovery type makes it easy to duplicate a database that was previously   backed up. You can only mark a single item for this operation.

Also, you can copy a system database, but you cannot overwrite it.

NMM does not support copy recovery of filestream databases.

Types of recovery mode

Description

Normal

The normal restore mode instructs SQL Server to leave the   database in an operational state after the restore completes. This then   enables database reads and writes. The normal restore mode is the default   mode NMM uses when restoring a database.

No-recovery

The no-recovery restore mode activates the SQL Server   NORECOVERY database restore option for the last stage restored. The   no-recovery restore mode places the database in a state that cannot be loaded   after the restore, but is still able to process additional transaction log   restore operations.

Standby

The standby restore mode activates the SQL Server STANDBY   database restore option for the last stage that is restored, which forces the   database to be in a readonly state between transaction log restore   operations. The standby restore mode provides an undo file for SQL Server to   use when rolling back the transactions.

Online

SQL Server provides the ability to perform a restore   operation while a SQL Server database is active. The database is completely   offline only while the primary file group is being restored. Once the primary   file group is restored, the database can be brought online while the rest of   the file groups are being restored, and then only the data that is being   restored is unavailable. The rest of the database remains available during this   type of restore. Earlier versions of SQL Server require that you bring a   database offline before you restore the database.

Program

Log file

Location

nsrsqlsv.exe

nsrsqlsv.log

nsr\applogs

nsrsqlrc.exe

nsrsqlrc.log

nsr\applogs