SQL backup statement

-- Full backup

Backup database northwindcs to disk = 'G: \ backup \ northwindcs_full_20070908.bak'

-- Differential backup

Backup database northwindcs to disk = 'G: \ backup \ northwindcs_diff_20070908.bak 'with differential

-- Log backup. By default, logs are truncated.

Backup log northwindcs to disk = 'G: \ backup \ northwindcs_log_20070908.bak'

-- Log backup without log Truncation

Backup log northwindcs to disk = 'G: \ backup \ northwindcs_log_20070908.bak 'with no_truncate

-- Log truncation is not retained

Backup log northwindcs with no_log

-- Or

Backup log northwindcs with truncate_only -- the log file will not become smaller after the truncation -- it is necessary to compress the log file.

-- File backup

Exec sp_helpdb northwindcs

-- View data files

Backup database northwindcs file = 'northwindcs'

-- Logical name of the data file

To disk = 'G: \ backup \ northwindcs_file_20070908.bak'

-- File Group Backup

Exec sp_helpdb northwindcs

-- View data files

Backup database northwindcs filegroup = 'primary'

-- Logical name of the data file

To disk = 'G: \ backup \ northwindcs_filegroup_20070908.bak 'with init

-- Split the backup to multiple targets -- no target can be lost during restoration.

Backup database northwindcs to disk = 'G: \ backup \ northwindcs_full_1.bak ', disk = 'G: \ backup \ northwindcs_full_2.bak'

-- Image backup -- each target is the same

Backup database northwindcs to disk = 'G: \ backup \ northwindcs_0000_1.bak 'mirror to disk = 'G: \ backup \ northwindcs_0000_2.bak' with format

-- Format the target during the first image backup

-- Image backup to local and remote

Backup database northwindcs to disk = 'G: \ backup \ northwindcs_0000_1.bak 'mirror to disk =' \ 192.168.1.200 \ backup \ northwindcs_0000_2.bak 'with format

-- Generate a backup file every day

Declare @ path nvarchar (2000) set @ Path = 'G: \ backup \ northwindcs_full _ '+ convert (nvarchar, getdate (), 112) +'. Bak'

Backup database northwindcs to http://www.hack58.net/Article/html/3/7/2008/mailtdisk=@Path

-- From norecovery or

-- Standby mode restores the database to available Restore database northwindcs_bak with recovery

-- View the backup set in the target backup

Restore headeronly from disk = 'G: \ backup \ northwindcs_full_20070908.bak'

-- View the information of the first backup set of the target backup.

Restore filelistonly from disk = 'G: \ backup \ northwindcs_full_20070908_2.bak 'with file = 1

-- View the volume label of the target backup

Restore labelonly from disk = 'G: \ backup \ northwindcs_full_20070908_2.bak'

-- Set password protection for backup

Backup database northwindcs to disk = 'G: \ backup \ northwindcs_full_20070908.bak 'with Password = '000000', init

Restore database northwindcs from disk = 'G: \ backup \ northwindcs_full_20070908.bak 'with Password = '000000'