backup database pubs to disk='e:\完全备份.bak'
backup database pubs to disk='e:\差异备份.bak' with differential
restore database pubs from disk='e:\完全备份.bak'with norecovery
restore database pubs from disk='e:\差异备份.bak' with recovery
backup log pubs to disk='e:\日志备份.bak'
在SQL2005中我使用如上语句进行备份没有问题。
restore database pubs from disk='e:\完全备份.bak' with norecovery
restore database pubs from disk='e:\差异备份.bak' with norecovery
restore log pubs from disk='日志备份.bak' with recovery然后我使用如上的语句进行还原,完全和差异都没问题。但是在使用事务日志恢复的时候,出现了如下错误:错误信息:
消息 3201,级别 16,状态 2,第 3 行
无法打开备份设备 'd:\program files\MSSQLServer\MSSQL\BACKUP\日志备份.bak'。设备出现错误或设备脱机。详细信息请参阅 SQL Server 错误日志。
消息 3013,级别 16,状态 1,第 3 行
RESTORE LOG 操作异常终止。
请问我是不是哪里做的不对。还是有的地方操作错误了,请指点下小弟!!!!
backup database pubs to disk='e:\差异备份.bak' with differential
restore database pubs from disk='e:\完全备份.bak'with norecovery
restore database pubs from disk='e:\差异备份.bak' with recovery
backup log pubs to disk='e:\日志备份.bak'
在SQL2005中我使用如上语句进行备份没有问题。
restore database pubs from disk='e:\完全备份.bak' with norecovery
restore database pubs from disk='e:\差异备份.bak' with norecovery
restore log pubs from disk='日志备份.bak' with recovery然后我使用如上的语句进行还原,完全和差异都没问题。但是在使用事务日志恢复的时候,出现了如下错误:错误信息:
消息 3201,级别 16,状态 2,第 3 行
无法打开备份设备 'd:\program files\MSSQLServer\MSSQL\BACKUP\日志备份.bak'。设备出现错误或设备脱机。详细信息请参阅 SQL Server 错误日志。
消息 3013,级别 16,状态 1,第 3 行
RESTORE LOG 操作异常终止。
请问我是不是哪里做的不对。还是有的地方操作错误了,请指点下小弟!!!!
create database Test
on primary
(
name='Test_data.mdf',
filename='d:\Test_data.mdf'
)
log on
(
name='Test_data.ldf',
filename='d:\Test_data.ldf'
)
--创建测试表
create table Test.dbo.ta(id int)
insert into Test.dbo.ta select id from sysobjects
--创建备份文件
backup database Test to disk='d:\Test.bak' with format
--创建一个NDF文件
alter database Test
add file
(
name='file',
filename='d:\test.ndf',
SIZE = 1MB
)
--关闭MSSQL服务,然后删除NDF文件然后再打开MSSQL服务,此时Test数据库被疑置啦!
--恢复数据疑置
--首先备份日志文件
backup log Test to disk='d:\Test_log.bak' with format,no_truncate
--利用文件组备份文件恢复被破坏的文件
restore database Test from disk='d:\Test.bak' with Norecovery
--恢复日志文件
restore Log Test from disk='d:\Test_log.bak' with RECOVERY --TipS
--如果已经建立ndf文件的话那么恢复日志文件的话用下面的这句话
--只不过将with RECOVERY-->WITH REPLACE
restore Log Test from disk='d:\Test_log.bak' WITH REPLACE 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2010/04/07/5457193.aspx
=====>
restore log pubs from disk='e:\日志备份.bak' with recovery
restore log pubs from disk='日志备份.bak' with recovery
没有指定具体路径
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数据库中。