backup database msdb to tapebackup with noformat, unload

Backup a database to a tape device using a variable

backup database @mydb to tapebackup with format, unload

File or Filegroup backup

Backing up a file or filegroup can give you a very efficient method of capturing changes. This does take significant advance planning. The DBA can place tables, indexes, and image or text data into several files or filegroups. For very large databases, backups of individual files or filegroups can protect your data while also reducing the amount of time it takes for a backup by giving you the ability to backup only volitile data. If you know that a set of tables in your database are going to be static, place them into their own file or filegroup. If there are a set of tables that only get updated periodically, place these into another file or filegroup. Those tables that receive frequent modifications should be placed into their own file or filegroup. This gives you the ability to segment your backup and capture only those tables that are changing.

The files or filegroups that contain static data can be backed up once or each time a modification is made. After that, they do not need to consume space and time in your backups. The files or filegroups that receive periodic changes can be backed up after each period in order to capture those changes. This removes another burden from yoru normal backups. The files or filegroups that are frequently changing can then be backed up at a regular interval such as nightly.

The combination of all of these files still encompasses the entire database and puts it into the same consistent state as a full or differential backup would, but they place a much lower burden on the backup in terms of space and time since you do not have to back up data that is not changing.

When backing up files or filegroups, you must back up objects as a whole. If you have a table in one file, its indexes in another file, and any image or text data in a third file, all three files must be backed up together.

The command to backup a file or filegroup is as follows with the myriad options the same as described in the options table previously.

BACKUP DATABASE {database_name | @database_name_var}<file_or_filegroup> [,...n]TO <backup_device> [,...n][WITH [BLOCKSIZE = {blocksize | @blocksize_variable}][[,] DESCRIPTION = {text | @text_variable}][[,] EXPIREDATE = {date | @date_var}| RETAINDAYS = {days | @days_var}][[,] FORMAT | NOFORMAT][[,] {INIT | NOINIT}][[,] MEDIADESCRIPTION = {text | @text_variable}][[,] MEDIANAME = {media_name | @media_name_variable}][[,] [NAME = {backup_set_name | @backup_set_name_var}][[,] {NOSKIP | SKIP}][[,] {NOUNLOAD | UNLOAD}][[,] [RESTART][[,] STATS [= percentage]]]

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32