(Sql Server高级技巧)使用SQL语句实现备份与还原

(1)
backup database BBS
to disk = 'c:\northwind.bak'
--filelistonly查看备份文件信息
restore filelistonly
from disk = 'c:\northwind2.bak'
restore database BBS1
from disk = 'c:\northwind2.bak'
with move 'BBS_Data' to 'C:\BBS1.mdf', --第一次加载往往

需要move指定新建的位置,否则后面的运行出错
move 'BBS_Log' to 'C:\BBS1.ldf'
go

(2)
restore database BBSkangco
from disk = 'C:/BBSkangco.bak'
with NORECOVERY --须先指定NORECOVERY
restore LOG BBSkangco
from disk = 'C:/BBSkangco.bak'
--with recovery
with stopat='2007-05-23 17:38:40'--还原的时间点

(3)
backup database BBSkangco to disk='c:\aa.bak' with init
--如果介质为空，SKIP 和 INIT 与 BACKUP 或 DUMP 语句的 FORMAT 子句作用相

同，写新介质头。如果介质不为空，则 SKIP 和 INIT 不写新介质头。
backup log BBSkangco to disk='c:\aa_log.bak' with init
restore database sbxz from disk='c:\aa.bak' with replace,norecovery
restore log sbxz from disk='c:\aa_log.bak' with recovery,stopat='2006-

03-01 20:19:00'

(4)
实例:根据还原时间点来还原资料库

declare @stopat_time datetime
declare @str_sql varchar(4000)
set @stopat_time='2005-04-13'
set @str_sql='restore log 数据库名称
from 日志备份
with recovery,stopat ='+@stopat_time+''
exec (@str_sql)

--你也可以把上边的封装成存储过程,这样你可以根据你的需要来还原了.