Sqlserver: 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 Truncation
-- Contract if necessary
-- 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 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_1__1.bak'
Mirror
To disk = 'G: \ backup \ northwindcs_1__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_1__1.bak'
Mirror
To disk = '\ 192.168.1.200 \ backup \ northwindcs_assist_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' Backup: backup database dbname to disk = '; D: \ DBN. ';
Recovery: Restore database dbname from disk = '; D: \ DBN. '; Recovery can be successful only in single-user mode. We need to disconnect other users and switch to another database. |
