資料備份的幾個注意點

建立備份裝置

use master

go

exec sp_addumpdevice 'disk','data123','d:\123.bak'

備份資料

backup database data1 to disk='d:\123.bak'

備份日志

backup log data1 to disk='d:\123.bak'

檢視備份情況

restore headeronly from disk='d:\123.bak'

還原完整資料及日志

restore database data1 from disk='d:\123.bak' with file=1,replace,norecovery

restore log data1 from disk='d:\123.bak' with file=2,replace,norecovery

restore log data1 from disk='d:\123.bak' with file=3,replace,recovery

覆寫備份（init意為初始化不加參數為追加備份）

backup database data1 to disk='d:\123.bak' with init

按時間點還原完整資料及日志

restore log data1 from disk='d:\123.bak' with file=2,recovery,stopat='2015-03-10 20:30:35.100'

資料庫壞掉後直接備份日志

backup log data1 to disk='d:\123.bak' with no_truncate

截斷事務日志

backup log data1 with no_log

差異備份

backup database data1 to disk='d:\123.bak' with Differential

備份到兩塊硬碟

exec sp_addumpdevice 'disk','data1','d:\data1.bak'

exec sp_addumpdevice 'disk','data2','e:\data2.bak'

backup database data to data1,data2 with medianame='data12'

restore database data from data1,data2 with repalce

鏡像備份還原

exec xp_cmdshell 'mkdir c:\BackupOrginal'

exec xp_cmdshell 'mkdir e:\BackupMirror'

exec sp_addumpdevice 'BackupOraginal' to disk='c:\BackupOrginal\BackupOrginal.bak'

exec sp_addumpdevice 'BackupMirror' to disk='e:\BackupMirror\BackupMirror.bak'

backup database data to BackupOrginal mirror to BackupMirror with format

restore database data from BackupOrginal

restore database data from BackupMirror

快照

create database data1200 on 

(name=N'data1' file=N'd:\data123.ss') 

as snapshot of data1

Go

restore database data1 from database_snapshot 'data1200'

啟動單使用者模式

在服務裡加參數 -m

恢複master

本文轉自 qvodnet 51CTO部落格，原文連結:http://blog.51cto.com/bks2015/1619160