数据备份的几个注意点

创建备份设备

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 database data1 from disk='d:\123.bak' with file=1,replace,norecovery

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