Dump and Load

DUMP i.e Backup

When you dump a database, it backups the whole database including the the data file

and the transaction log file.

You could dump the database even while users are still accessing the database,

user will notice a slight degradation of performance.

Command,

dump database <db_name> to '/path/to/backupfile.bak/'

go

Transaction Dump

Backs up the transaction log, it is sort of incremental backup, i.e backs up all the

changes made to the database since the last full database backup or last

transaction backup. You could dump transaction only if log are stored in different segment.

Various Options with dump transaction

1>dump tran <db_name) to '/path/to/backupfile.bak' -- This will backup the transaction log to the mentioned file

2>go

1>dump tran <db_name> with truncate_only --removes the inactive portion without making a

2>go backup copy and logs the deallocation of the inactive pages

1>dump tran <db_name> with no_log --removes the inactive portion without making a backup.

2>go

1>dump tran <db_name> to '/path/to/backup.bak' --In case of media failure and log segments resides on seprate device

with no_truncate --use this option to backup the transaction log

2>go