创建备份设备
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
