In addition to having multiple data and transactoin log files, the database can have one or more filegroups. Transaction logs never reside on a filegroup. Filegroups are utilized to take advantage of charateristics of yoru disk drives in order to improve performance. An administrator can create a filegroup on a particular set of disk drives that then assign certain tables, indexes, or text and image data to that filegroup.

No two databases can share the same file or filegroup.

The transaction log contains a complete record of the DML, Data Manipulation Language, and DDL, Data Definition Language that has been issued against the database. All modifications are written to the transaction log first. Periodically, SQL Server will flush these changes to the disk during the checkpoint process. The transactions are then marked as completed, but retained in the transaction log. This means that the transaction log contains a complete record of all changes made since the last full database backup.

At this point, you may be asking what the physical storage has to do with backing up the database. It has everything to do with it. SQL Server 7.0 has completely changed the way data is stored. With this change came significant enhancements to the way data is backed up. You can now backup a database, transaction log, file, or filegroup individually.

Backing up a database

The full command to backup and database is as follows:

BACKUP DATABASE {database_name | @database_name_var} TO <backup_device> [,...n][WITH [BLOCKSIZE = {blocksize | @blocksize_variable}][[,] DESCRIPTION = {text | @text_variable}][[,] DIFFERENTIAL][[,] 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]]]

At first look, this can be very intimidating. Backups are not this complicated and many of these options only apply if backing up to tape. What you are seeing here are the myriad options that are available for a backup. One of the significant enhancements to the backup command has been the inclusion of variables. In preveious verisons of SQL Server, almost every DBA built a custom script to backup databases. This usually consisted of constructing the backup command inside of a variable and then using the exec statement to execute that command. This was done so that a single script could serve to backup all databases on a server without having to modify the script each time a database was created or dropped. You now have the flexibility to use a static value or a variable for each one of the parameters accepted by the backup command.

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