There is no “incremental” backup type in SQL Server.Partial Backup, partial differential backup, striped backup, mirrored backup, copy-only backup,structure backup, cold backup, full-text backup, ile and filegroup backup
Partial Differential Backup
The extents that have changed in filegroups that are not read-only are captured in this type of backup. This includes the primary filegroup and any read/write filegroups that are defined at the time of the backup. Like differential data- base backups, these backups also require a differential base, but it must be a single differ- ential base.
Structure Backup
I find it useful to create a backup of the T-SQL script that could re-create the structure of the database itself. There are times when a database becomes corrupted in such a way that there is no way to bring it online, but only to start it in what is termed emergency mode. While in that mode, you can copy data out of the database, but that’s about it. If you don’t have an empty, clean destination for that data, the emergency mode won’t do you any good.
Cold Backup
I’ve run into situations where the DBA performs a cold backup only—that is, he stops SQL Server, backs up all of the data files, and then restarts the SQL service. The obvious problem is that the technique makes the database unavailable while the backup is occurring. Unless you have a large window for backup operations, this may not be an option.
Full-Text Backup
In SQL Server 2000, full-text catalogues were not included in any backup unless the appropriate directories were backed up with the Microsoft Search service turned off. In SQL Server 2005, full-text catalogues are backed up automatically whenever a full or filegroup backup occurs (given that the catalogues are associated with the filegroup being backed up). It can be convenient to have those catalogues included automatically, but they can increase the size of the full or filegroup backup significantly. If your database makes use of full-text search, be sure to keep this fact in mind.
Securing Backups
The BACKUP DATABASE and BACKUP LOG statements support a PASSWORD property:
BACKUP DATABASE AdventureWorks TO DISK = 'D:\data\AW.bak' WITH PASSWORD='monona
It is intended more to prevent the accidental restore of an incorrect backup set. it is probably better to encrypt within the database rather than simply relying on some sort of backup encryption or password.
File and Filegroup Backups
File and filegroup backups are targeted at databases that contain more than one filegroup. In these situations, the filegroup or files in the filegroups can be backed up indepen- dently. If a filegroup is backed up, then all the files defined in the filegroup are backed up.
Summary
A full backup copies all pages in the database that are in allocated extents. A differential backup copies all pages which have been modified since the last full backup. A transaction log backup copies the contents of the transaction log since the last transaction log backup.