SQL还原
来源:互联网 发布:mac草叶壁纸 编辑:程序博客网 时间:2024/04/29 22:48
SQL还原
======================================================================
1、验证备份
------------------------------------------------------------
restore headeronly from bak3
restore filelistonly from bak3 with file=1
restore labelonly from bak3
restore verifyonly from bak3
----------------------------------------------------------------------
2、从备份中还原
-------------------------------------------------------------------------
restore headeronly from bak1
restore database d1 from bak1 with file=2 --从完全备份中恢复
----------------------------------------------------------------------
restore headeronly from bak2 --从差异备份中恢复
restore database d2 from bak2 with file=1,norecovery
restore database d2 from bak2 with file=5,recovery
----------------------------------------------------------------------
restore headeronly from bak3 --从日志备份中恢复
restore database d3 from bak3 with file=1,norecovery
restore log d3 from bak3 with file=2,norecovery
restore log d3 from bak3 with file=3,norecovery
restore log d3 from bak3 with file=4,norecovery
restore log d3 from bak3 with file=5,recovery
----------------------------------------------------------------------
restore database d3 from bak3 with file=1,norecovery --恢复到指定时间
restore log d3 from bak3 with file=2,norecovery
restore log d3 from bak3 with file=3,norecovery
restore log d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000'
----------------------------------------------------------------------
restore database d5 filegroup='FG2' from bak5 with file=4,norecovery --还原文件组备份
restore log d5 from bak5 with file=5,norecovery
restore log d5 from bak5 with file=7,recovery
----------------------------------------------------------------------
restore headeronly from bak6 --还原文件备份
restore database d5 file='d5_data3' from bak6 with file=6,norecovery
restore log d5 from bak6 with file=7,norecovery
restore log d5 from bak6 with file=9,recovery
----------------------------------------------------------------------
restore database d5 from bak6 with replace --删除现有数据库,从备份中重建数据库
----------------------------------------------------------------------
create database d6 --move to将数据库文件移动到新位置
on primary
(name=d6_data,
filename='E:/Program Files/Microsoft SQL Server/MSSQL/data/d6_Data.MDF',
size=2MB)
log on
(name=d6_log,
filename='E:/Program Files/Microsoft SQL Server/MSSQL/data/d6_log.ldf',
size=2MB)
go
backupdatabase d6 to bak6 with init
drop database d6
restore database d6 from bak6
with move 'd6_data' to 'e:/data/d6/d6_data.mdf',
move 'd6_log'to 'e:/data/d6/d6_log.ldf'
sp_helpdb d6
----------------------------------------------------------------------
3、分离与重连接数据库
--------------------------------------
sp_detach_db 'd6'
sp_attach_db 'd6','e:/data/d6/d6_data.mdf','e:/data/d6/d6_log.ldf'
--------------------------------------
sp_detach_db d6
go
create database d6
on primary
(filename='e:/data/d6/d6_data.mdf')
for attach
go
----------------------------------------------------------------------
4、恢复损坏的系统数据库
----------------------------------------------------------------------
1)先备份MASTER、MSDB
2)停止SQL服务,将MASTER数据库文件删除或者重命名。这样,SQL服务将不能启动。
3)系统数据库的还原
-----------------------------------------------
(1)如果SQL服务还能启动,则从备份中恢复系统数据库。
(2)如果SQL服务不能启动,则需要重建系统数据库。
使用SQL文件夹TOOLS/BINN目录下的Rebuildm.exe重建master数据库。
(3)创建备份设备,指向以前的备份设备。
(4)以单用户模式启动SQL
cd programe files/microsoft sql server/mssql/binn
sqlservr.exe -c -m
(5)进查询分析器,从备份中恢复master数据库。
restore database master from masterbak
restore database msdb from disk='e:/bak/msdb.bak'
MASTER还原后,SQL中用户数据库的信息也会恢复。
(6)如果MASTER没有备份,则需要用sp_attach_db命令将用户数据库附加到新的MASTER数据库中。
----------------------------------------------------------------------
5、自动化备份实现(要将sqlserveragent服务设置为自动启动,并启动该服务)
----------------------------------------------------------------------
我们日常使用的MOSS进行公司部门站点的信息管理与收发,后台使用MS SQL2005.
计划采用完全备份,差异备份和日志备份来实现MOSS数据的日常备份和管理.
计划如下:
采用完全恢复模型。
备份设备:disk为主,可以用本地磁盘或网络磁盘备份。
备份方法:完全备份+差异备份+事务日志备份。
备份日程:完全备份每天进行一次,差异备份每四个小时一次,事务日志备份每三十分钟一次。备份网络磁盘仅保留近一周的备份文件。
备份文件命名:
完全备份:数据库实例名称+“_full.bak”
差异备份:数据库实例名称+“_diff_”+hh+”.b”
事务日志备份:数据库实例名称+“_log_+hhmm
备份路径:网络磁盘/YYMMDD/
下文以eip数据库实例为例来说明操作步骤。
每天检查并删除过时的备份文件
--Function:full backup database
--1 Variable declaration
declare @DbPath varchar(500)
declare @DbName sysname
declare @FileName varchar(500)
--2 Initialize variables
set @DbName = 'wsseip' --custom
set @DbPath='//172.22.8.121/D$/dbbackup/'+@DbName+'/'+convert(varchar(10),getdate(),112) +'/'
set @FileName = @DbPath +@DbName+ '_full.bak'
--3 Net connect and create file
exec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'
EXEC master.dbo.xp_create_subdir @DbPath
--4 Create backup
backup database EIP to disk=@FileName with init
go
----------------------------------------------------------------------
每天做一次完全备份
--Function:del backup database
--1 Variable declaration
declare @DbPath varchar(500)
declare @DbName sysname
declare @FileName varchar(500)
declare @CmdShell varchar(500)
--2 Initialize variables
set @DbName = 'wsseip' --custom
set @DbPath='//172.22.8.121/D$/dbbackup/'+@DbName+'/'+convert(varchar(10),dateadd(day,-5,getdate()),112)
set @CmdShell ='rd /S /Q ' + @DbPath
--3 Net connect and create file
exec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'
--4 Del log backup
exec master..xp_cmdshell @CmdShell --删除5天前的备份,也就是只保留5个最新备份
go
----------------------------------------------------------------------
每4个小时做一次差异备份
--Function:diff backup database
--1 Variable declaration
declare @DbPath varchar(500)
declare @DbName sysname
declare @FileName varchar(500)
--2 Initialize variables
set @DbName = 'wsseip' --custom
set @DbPath='//172.22.8.121/D$/dbbackup/'+@DbName+'/'+convert(varchar(10),getdate(),112) +'/'
set @FileName = @DbPath +@DbName+ '_diff_'+cast(datepart(hour,getdate()) as varchar)+'.bak'
--3 Net connect and create file
exec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'
EXEC master.dbo.xp_create_subdir @DbPath
--4 Create diff backup
backup database EIP to disk=@FileName with differential --差异备份数据库
go
----------------------------------------------------------------------
每30分钟做一次事物日志备份
--Function:diff backup database
--1 Variable declaration
declare @DbPath varchar(500)
declare @DbName sysname
declare @FileName varchar(500)
--2 Initialize variables
set @DbName = 'wsseip' --custom
set @DbPath='//172.22.8.121/D$/dbbackup/'+@DbName+'/'+convert(varchar(10),getdate(),112) +'/'
set @FileName = @DbPath +@DbName+ '_log_'+cast(datepart(hour,getdate()) as varchar)+cast(datepart(minute,getdate()) as varchar)
--3 Net connect and create file
exec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'
EXEC master.dbo.xp_create_subdir @DbPath
--4 Create log backup
ALTER DATABASE eip SET RECOVERY FULL
backup log eip to disk=@FileName --日志备份数据库
go
----------------------------------------------------------------------
---------这是我拷贝别人的,没有测试的,如果谁要用的话,最好自己去亲自去测试哈,免得出现不必要的错误!
- SQL还原
- SQL还原
- sql 还原数据库
- sql server2005还原数据库
- sql备份还原
- 还原SQL数据库
- SQL2000还原SQL数据库
- SQL数据库还原语句
- 还原SQL失败
- sql 数据库备份还原
- SQL语句备份还原
- sql 还原代码
- SQL备份与还原
- SQL备份和还原
- SQL Server2005 还原数据库
- sql 备份还原数据库
- sql 备份还原数据库
- 备份还原SQL
- 工资纳税系统(C++)
- SQL备份
- 复活节撒谎
- 看到WEIBO上面好几个牛人在BS .NET,实在无语。
- 新手
- SQL还原
- MIPS中的分支延迟槽
- 我们工作到底为了什么
- C#学习笔记四 Windows窗体应用程序
- Java的多线程编程模型4--synchronized
- 软件测试工作真的很简单无技术难度吗
- 成为一名优秀程序员所需要知道的15件事
- 原型法
- 四种流行的AJAX框架jQuery,Mootools,Dojo,ExtJS的对比