Partial Backup of MS SQL Server 2008 Database

Requirement:

Backup to be part of the regular backup routine whilst continuing to append the backup to the media.

Add the statement below in the script:

BACKUP DATABASE [TT_DATA] TO DISK = N'C:\Backup\TT_DATA.bak', [Fullback] WITH NOFORMAT, NOINIT, NAME = D'TT_DATA-Full Database Backup',SKIP, NOREHIND, NOUNLOAD, DIFFERENTIAL, COPY_ONLY

This statement will perform a differential backup that will back up the changes made in the database since the last full backup. This statement also includes the differential parameter and the copy_only parameter. If both of these parameters are in the statement, the copy_only parameter is ignored and the differential parameter is used. When you perform a full backup, it resets the base backup. A differential backup takes all data that has changed since the last full backup. If you were to perform another full backup in between the current backup sequence of Full, Differential and Transaction log backups, you would disrupt the regular backup schedule. Copy-only backups are useful for making a backup to transport to another location because it will not affect the current sequence of regular backups.You can use the backup database command to back up a database.

The following are options with the backup database command:

copy_only -allows a copy-only backup.
differential -backs up only the portions of the database or file that have changed since the last full backup. If the DIFFERENTIAL parameter and COPY_ONLY parameter are used together, the COPY_ONLY parameter is ignored.
disk -specifies the destination of the backup.
noformat -does not format the media.
init -initializes the media and does not allow backups to be appended to media.
noinit -does not initialize the media and allows backups to be appended to media.
name -specifies name of the backup.
skip -Ignores the backup set expiration.
norewind -does not rewind media.
NOUNLOAD - does not unload media after the backup operations is complete

When performing a partial restore that requires a point-in-time restore you must include one of the stopat , stopatmark , or stopbeforemark options. If a FILESTREAM filegroup is excluded from a point-in-time restore, the point-in-time restore will fail. For the point-in-time restore to continue, you can specify the continue_after_error option together with the stopat , stopatmark , or stopbeforemark . This will allow the restore to continue, but the FILESTREAM filegroup will become unrecoverable.