3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'cmssql7backupMyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack
从备份中还原 ------------------------------------------------------------------------- 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 ----------------------------------------------------------------------
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'cmssql7backupMyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
-------------------------------------------------------------------------
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
----------------------------------------------------------------------
backup database dbname
to disk = 'xx:\xx\xxx.bak'还原
restore database dbname
from disk = 'xx:\xx\xxx.bak'
还原需要删除需要还原的数据库更多信息参考联机帮助
同意楼上的,备份的办法很多,我觉得这个办法最简单:备份
1. 停止SQL服务
2. COPY数据库对应的MDF LDF文件到备份目录
3. 启动SQL服务
MDF/LDF文件默认在 C:\Program Files\Microsoft SQL Server\MSSQL\Data\ 目录下恢复
1. 停止SQL服务
2. COPY备份目录中的MDF LDF文件,覆盖原来的文件
3. 启动SQL服务