Appreciative SQL Server Backup Kinds

SQL Server backups, in itself, may be a large subject; thus large, there area unit multiple books written regarding them. During this article, however, we tend to area unit attending to specialize in the categories of backups that area unit on the market to United States, and perceive a way to decide what we want, and what aspects we tend to base that call on. This understanding would, in turn, facilitate United States decide our backup-and-restore strategy.

Following area unit the foremost common kinds of backups on the market in SQL Server:

  1. Full
  2. Differential
  3. Transaction log
  4. Tail Log backup

There area unit different backup sorts on the market as well:

  1. Copy-only backup
  2. File backups
  3. Partial backups.

Full backups:

A full backup, because the name implies, backs up everything. It’s the inspiration of any reasonably backup. This can be an entire copy that stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full back up, you may be able to simply restore a info in precisely identical kind because it was at the time of the backup.

A full backup creates an entire backup of the info moreover as a part of the dealing log; therefore the info will be recovered. this permits for the only type of info restoration, since all of the contents area unit contained in one single backup.

A full backup should be done a minimum of once before any of {the different|the opposite} kinds of backups will be run—this is that the foundation for each other reasonably backup.

How to produce full info backup exploitation T-SQL:

The BACKUP info is that the command wont to produce a full info backup. It needs a minimum of 2 input parameters: the info name and also the backup device.

Following is that the example for a full info backup to be hold on in an exceedingly device:

To DISK=’f:\PowerSQL\SQLShackDemoATC.BAK’
MEDIANAME = ‘Native_SQLServerBackup’,
NAME = ‘Full-SQLShackDemoATC backup’;

Full info Backup to Multiple files:

Sometimes in some instances, we’re restricted by the quantity of house we’ve. What if we tend to needed to back up a complete info that’s huge? Or what if we’ve to repeat the backup files over the network? It’d be an honest plan in these cases to separate the backup into smaller chunks—each being a separate file.

DISK = ‘f:\PowerSQL\SQLShackDemoATC_1.BAK’,
DISK = ‘f:\PowerSQL\SQLShackDemoATC_2.BAK’,
DISK = ‘f:\PowerSQL\SQLShackDemoATC_3.BAK’,
DISK = ‘f:\PowerSQL\SQLShackDemoATC_4.BAK’
WITH INIT, NAME = ‘FULL SQLShackDemoATC backup’, STATS = 5

If you’d prefer to produce a mirror copy of the backup file:

TO DISK = ‘F:\PowerSQL\ProdSQLShackDemo_1.BAK’
MIRROR TO DISK = ‘F:\PowerSQL\ProdSQLShackDemo_2.BAK’

You can, in fact, have up to 3 mirror copies:

TO DISK = ‘F:\PowerSQL\ProdSQLShackDemo_1.BAK’
MIRROR TO DISK = ‘F:\PowerSQL\ProdSQLShackDemo_2.BAK’
MIRROR TO DISK = ‘F:\PowerSQL\ProdSQLShackDemo_3.BAK’
MIRROR TO DISK = ‘F:\PowerSQL\ProdSQLShackDemo_4.BAK’

Differential Backups:

A differential info backup is that the superset of the last full backup and contains all changes that are created since the last full backup. So, if there area unit only a few transactions that have happened recently, a differential backup could be tiny in size, however if you’ve got created an oversized variety of transactions, the differential backup can be terribly massive in size.

As a differential backup doesn’t keep a copy everything, the backup typically runs faster than a full backup. A differential info backup captures the state of the modified extents at the time that backup was created. If you produce a series of differential backups, a frequently-updated info is probably going to contain completely different information in every differential. Because the differential backups increase in size, restoring a differential backup will considerably increase the time that’s needed to revive a info. Therefore, it’s counseled to require a replacement full backup, at set intervals, to ascertain a replacement differential base for the info.

Differential backups save space for storing and also the time it takes for a backup. However, as information changes over time, the differential backup size conjointly will increase. The longer the age of a differential backup and bigger the dimensions and at some purpose in time it should reach the dimensions of the total backup. an oversized differential backup loses the benefits of a quicker and smaller backup because it needs the total backup to be fixed up before restoring the recent differential backup. Typically, we might restore the foremost recent full backup followed by the foremost recent differential backup that’s supported that full backup.

How to produce Differential info backup exploitation T-SQL:

The BACKUP info command is employed with the differential clause to form the differential info backup. It needs 3 parameters:

  1. Database name
  2. Backup device
  3. The DIFFERENTIAL clause

For example,

To DISK=’f:\PowerSQL\SQLShackDemoATC_Diff.BAK’
MEDIANAME = ‘Native_SQLServerDiffBackup’,
NAME = ‘Diff-SQLShackDemoATC backup’;

Transaction Log Backup:
The log backup, as its name implies, backs up the dealing logs. This backup sort is feasible solely with full or bulk-logged recovery models. A dealing log file stores a series of the logs that give the history of each modification of information, in an exceedingly info. A dealing log backup contains all log records that haven’t been enclosed within the last dealing log backup.

It permits the info to be recovered to a particular purpose in time. this implies that the dealing log backups area unit progressive and differential backups area unit accumulative in nature. If you wish to revive the info to a particular purpose in time, you would like restore a full, recent differential, and every one the corresponding dealing log records that area unit necessary to make the info up to it specific purpose, or to {a purpose|some extent|a degree} terribly near to the required point in time, simply before the incidence of the accident that resulted within the information loss. This series of modifications is contained and maintained exploitation LSN (Log Sequence Number) within the log chain. A log backup chain is AN unbroken series of logs that contain all the dealing log records necessary to recover a info to some extent in time. A log chain perpetually starts with a full info backup and continues till for reason it breaks the chain (for example, ever-changing the recovery model of info to easy, or taking an additional full backup), therefore by preventing log backups from being taken on the info till another full (or differential) backup is initiated for that info.

How to produce Transactional log backup exploitation T-SQL:

The BACKUP LOG command is employed to backup the dealing log. It needs the info name, the destination device and also the dealing LOG clause to initiate the dealing log backup.

To DISK=’f:\PowerSQL\SQLShackDemoATC_Log.trn’
MEDIANAME = ‘Native_SQLServerLogBackup’,
NAME = ‘Log-SQLShackDemoATC backup’;

Tail log backups:

In the event of a failure, once you would like the info to urge keep a copy and running, and also the info is working fully or BULK_LOGGED recovery model, it’s perpetually straightforward to start out the recovery operation and begin restoring the backups. however before that, the primary action to be taken when the failure is what’s known as as a tail log backup of the live dealing log.

This is AN intermediate step that we want to require before we tend to begin the restoration. This method is termed tail log backup restoration.

USE master;
— create a tail-log backup
TO DISK = ‘f:\PowerSQL\SQLShackDemoATCTailLog.log’

The WITH CONTINUE_AFTER_ERROR clause can force SQL Server to store the log file, albeit it’s generating a slip-up.

Copy Only backup:
A copy-only backup may be a special style of full backup, that is freelance of the standard sequence of backups. The distinction between copy-only and a full backup is that a copy-only backup doesn’t become a base for following differential backup.

A full backup works on all info recovery models. Copy-only backup, on the opposite hand, is applicable solely to a full or bulk-logged recovery models. The restoration of a copy-only backup is not any completely different than a standard restoration method.

Performing the copy-only backup is pretty easy. The syntax would look one thing like this:

To DISK=’f:\PowerSQL\SQLShackDemoATC_1.BAK’
MEDIANAME = ‘Native_SQLServerFullBackup’,
NAME = ‘Full-SQLShackDemoATC backup’;
TO DISK = ‘f:\PowerSQL\SQLShackDemoATCCopyOnly.log’

The BACKUP LOG command with the COPY_ONLY possibility generates a copy-only log backup. It doesn’t involve in dealing log truncation.

It is necessary to use “COPY_ONLY” backup possibility so as to preserve the info backup sequence.

Partial backups:
Partial backups area unit one in every of the least-used backup strategies on the market in SQL Server. All info recovery models support partial backups, however partial backups area unit principally utilized in the easy recovery model so as to boost flexibility once backing up massive databases that contain read-only file groups.

The READ_WRITE_FILEGROUPS possibility is employed with the BACKUP info command. This command is for partial backup. It processes the backup of read-write file teams.

SQLShackPartialBackup is that the info created with primary and secondary file teams. Let’s use this info for this demo.

( NAME = N’SQLShackPartialBackup_1′,
FILENAME = N’f:\PowerSQL\SQLShackPartialBackup_1.mdf’ ,
SIZE = 5000KB , FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N’SQLShackPartialBackup_2′,
FILENAME = N’f:\PowerSQL\SQLShackPartialBackup_2.mdf’ ,
SIZE = 5000KB , FILEGROWTH = 1024KB )
( NAME = N’SQLShackPartialBackup_Log’,
FILENAME = N’f:\PowerSQL\SQLShackPartialBackup_log.ldf’ ,
SIZE = 1024KB , FILEGROWTH = 10%)

Let’s modification the recovery model of the info to easy exploitation the subsequent ALTER statement


Now, set the secondary file-group to READONLY mode


Initiate a backup exploitation the READ_WRITE_FILEGROUPS possibility

TO DISK = N'f:\PowerSQL\SQLShackPartialBackup_Full.bak'

We can see within the following screenshot that the SQLShackPartialBackup_2 file group isn’t been protected within the backup method.

File and File cluster Backups:

This topic has relevancy for SQL Server databases that contain multiple files or file groups. File backups of read-only file groups will be combined with partial backups. Partial backups embody all the read/write file groups and, optionally, one or a lot of read-only file groups.

Let’s produce a info with multiple files and file groups.

( NAME = N’SQLShackFileBackup_1′,
FILENAME = N’f:\PowerSQL\SQLShackFileBackup_1.mdf’ ,
SIZE = 5000KB , FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N’SQLShackFileBackup_2′,
FILENAME = N’f:\PowerSQL\SQLShackFileBackup_2.ndf’ ,
SIZE = 5000KB , FILEGROWTH = 1024KB )
( NAME = N’SQLShackFileBackup_Log’,
FILENAME = N’f:\PowerSQL\SQLShackFileBackup_Log.ldf’ ,
SIZE = 1024KB , FILEGROWTH = 10%)

The following examples demonstrates a way to produce the file-level backup of the files:

FILE = ‘SQLShackFileBackup_1’,
FILE = ‘SQLShackFileBackup_2’
TO DISK = ‘f:\PowerSQL\SQLShackGroupfiles.bak’;

The following example illustrates the total file backup of all the files in each of the first and secondary file-groups.

FILEGROUP = ‘Secondary’
TO DISK = ‘f:\PowerSQL\SQLShackGroupfilegroup.bak’;

Backup Set choices:
The following area unit the few choices that operate the info backup set that may be created exploitation backup info command

WITH choices:
The WITH clause is employed with the BACKUP command just in case we’ve further backup necessities. this selection allows backup compression. NO_COMPRESSION expressly disables the backup compression. Compression is turned off throughout backups by default.

ENCRYPTION: A coding algorithmic program will be nominative with BACKUP to secure the backup files hold on offsite. you’ll be able to specify NO_ENCRYPTION once you don’t would like backup coding.

Media set choices:

FORMAT: this selection wont to specify whether or not to write the media header info. The FORMAT clause can produce a replacement media backup set, whereas NOFORMAT can preserve all the knowledge.

INIT: INIT is employed to form a replacement backup set; NOINIT is employed for appending the backup to the prevailing backup set. The NOINIT parameter is employed principally once you backup the info to a tape device.

NAME: The NAME parameter is employed to spot the backup set.

SKIP: The skip parameter is employed to skip the expiration check on the backup set.

NOREWIND: This parameter is employed to stay a tape device open and prepared to be used

NOUNLOAD: This parameter is employed to instruct SQL Server to not unload the tape from the drive upon completion of the backup operation.

STATS: The STATS possibility is beneficial to urge the standing of the backup operation at regular stages of its progress.


Planning backups is comparatively less complicated for smaller databases. Because the databases grow in size the management of backup will quickly become a fancy and tedious job.

With coming up with and also the process right backup and restore/recovery strategy, we might place ourselves in an exceedingly smart position against any form of failure.

A full backup is often easier to revive however it’s a resource-intensive operation and takes longer to complete.

We’ve coated completely different backup sorts with examples during this article. This offers you a thought of the on the market backup sorts and also the basic purpose of every backup sort.

For example, for AN index make operation, contemplate the time needed and check that it solely happens right before a full back up in order that all of these changes ought not to be rolled up into differential backups.

To speed up a backup operation, contemplate moving historical information to archive file-groups and split the read-write information and read-only information into separate file-groups. This offers you the pliability to backup solely the read-write file-groups and their individual files.

Using the on the market backup compression choices, we will scale back the quantity of information that must be protected. This removes the additional burden on the storage and transfer resources required for the whole operation.

That’s all for currently. Keep tuned for a lot of updates!

Asad Mukhtar

Leave a Reply

Your email address will not be published. Required fields are marked *