日期:2014-05-18  浏览次数:20371 次

数据库备份与还原问题?
我有完全备份,差异备份,和日志备份三种备份
代码分别是:
完全: backup database mydb to disk=’E:\Backup\mydb.bak’
差异:backup database mydb to disk=’E:\Backup\mydb.bak' WITH DIFFERENTIAL,INIT
日志:backup log mydb_log to disk=’E:\backup\mydb.trn’

请问与之对应的三种恢复语句分别怎么写?
另外完全备份时我想把在这之前的数据库日志删掉(不是删备份),要是不删日志就会越来越长,请问怎么截断日志?

------解决方案--------------------
SQL code

RESTORE DATABASE [mydb ] FROM  DISK = N'E:\Backup\mydb.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [mydb ] FROM  DISK = N'E:\Backup\mydb.bak'  WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [mydb ] FROM  DISK = N'E:\Backup\mydb.trn'  WITH  FILE = 3,  NOUNLOAD,  STATS = 10
GO

------解决方案--------------------
SQL code

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 da