从备份中还原
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数据库中。
sql 从备份中还原
最新推荐文章于 2021-01-28 00:50:46 发布