SQL Server的三种备份类型详解(完整、差异和事务日志备份)

-- 执行完整备份
backup database [adventureworks] 
to disk = 'c:\backups\adventureworks_full.bak'
with compression, stats = 10;

-- 从完整备份恢复
restore database [adventureworks] 
from disk = 'c:\backups\adventureworks_full.bak'
with recovery;

-- 执行差异备份
backup database [adventureworks] 
to disk = 'c:\backups\adventureworks_diff.bak'
with differential, compression, stats = 10;

-- 从完整备份和差异备份恢复
restore database [adventureworks] 
from disk = 'c:\backups\adventureworks_full.bak'
with norecovery;

restore database [adventureworks] 
from disk = 'c:\backups\adventureworks_diff.bak'
with recovery;

-- 执行事务日志备份
backup log [adventureworks] 
to disk = 'c:\backups\adventureworks_log.trn'
with compression, stats = 10;

-- 从完整备份、差异备份和多个日志备份恢复
restore database [adventureworks] 
from disk = 'c:\backups\adventureworks_full.bak'
with norecovery;

restore database [adventureworks] 
from disk = 'c:\backups\adventureworks_diff.bak'
with norecovery;

restore log [adventureworks] 
from disk = 'c:\backups\adventureworks_log1.trn'
with norecovery;

restore log [adventureworks] 
from disk = 'c:\backups\adventureworks_log2.trn'
with recovery;