Backs up the specified database.

Syntax

---

BACKUP DATABASE <Name>
AS <BackupName>
TO STORE <StoreName>
[TABLES <TableName> [,<TableName>]]
[DESCRIPTION <Description>]
[COMPRESSION <Compression>]
[INCLUDE CATALOG [ONLY]]
[EXCLUDE PUBLISHED UPDATES]

<Compression> = 0..9

Usage

---

Use this statement to backup the specified database to a single backup file in the specified store. The store must have already been created using the CREATE STORE statement.

The TABLES clause is optional and allows you specify a subset of tables from the database for backup. The default behavior is to backup all tables in the database.


The COMPRESSION clause allows you to specify the compression level (0..9). The default level of compression is 6.

The INCLUDE CATALOG clause specifies that the database catalog should be backed up in addition to the table data. It is recommended that you always include this clause. At the very least, you should backup the database with this clause whenever any of the database objects changes. This includes views, procedures, and functions in addition to tables. You can use the ONLY clause to extend the INCLUDE CATALOG clause so that only the catalog is backed up.


Starting in 2.05, the EXCLUDE PUBLISHED UPDATES clause can be used to specify that you do not want published updates to be included in the backup, which can reduce the size of the backup significantly if there are a lot of published updates present for the tables being backed up.

Examples

---

-- This example backs up the entire
-- Accounting database with the catalog included

BACKUP DATABASE Accounting
AS "AccountingDB-DailyBackup-2007-03-12"
TO STORE "Backups"
INCLUDE CATALOG

Required Privileges

---

The current user must be granted the BACKUP privilege on the specified database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: