数据备份的几个注意点

  1. 云栖社区>
  2. 博客>
  3. 正文

数据备份的几个注意点

科技小能手 2017-11-12 04:03:00 浏览485
展开阅读全文

创建备份设备

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

网友评论

登录后评论
0/500
评论
科技小能手
+ 关注