Tuesday, July 29, 2008

Backup Database SQL SERVER 2005 Query

BACKUP DATABASE DBname
TO DISK = 'setBakUpPath' WITH NOFORMAT, NOINIT,
NAME = 'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10


NOINIT

Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

NOSKIP

Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. This is the default behavior.

SKIP

Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

REWIND

Specifies that SQL Server will release and rewind the tape. REWIND is the default.

NOREWIND

Specifies that SQL Server will keep the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations to a tape.

NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.

NOUNLOAD

Specifies that after the BACKUP operation the tape will remain loaded on the tape drive.

STATS [ = percentage ]

Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

No comments: