SQL2005 还原备份数据
一，读取备份文件
SQL:
Restore filelistonly from disk=’’ 读取差异和完整备份文件
Restore headeronly from disk=’’ 读取日志备份文件
SQL Litespeed：
Master..xp_restore_filelistonly @Filename=’’读取差异和完整备份文件
Master.. xp_restore_headeronly @Filename=’’ 读取日志备份文件
如果结果没有返回错误，基本上备份文件就是正确的。
注释：我们DB现在差异和完整备份一般都是用SQL Litespeed，日志备份用SQL.
例如：
Master..xp_restore_filelistonly @Filename='D:\PALBackUp\PAL_COMPSN_P80_DIFF20071008.BAK'----读取差异和完整备份文件
Restore headeronly from disk='D:\PALBackUp\PAL_COMPSN_P80_LOG20071008.TRN' ---读取日志备份文件
二、指定访问路径权限
Exec master..xp_cmdshell 'net use \\172.26.40.6\d$ PWD /user:fp-qsmc\administrator'
三、断开当前对DB PAL的操作
Declare @SQLStr nchar(100)
Declare @myspid smallint
Declare @mycursor cursor
Declare whocursor cursor for
Select spid from master..sysprocesses where dbid=db_id('PAL')
Set @mycursor=whocursor
Open @mycursor
Fetch next from @mycursor into @myspid
While @@fetch_status=0
Begin
--select @myspid
Set @SQLStr='kill '+cast(@myspid as char(3))
Execute sp_executesql @SQLStr
Fetch next from @mycursor into @myspid
End
Close @mycursor
Deallocate whocursor
四、还原Full完整备份
Declare @FullFileName Varchar(200)
Set @FullFileName='\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_FULL20080914.BAK'
EXEC master.dbo.xp_restore_database
@database='PAL',@Filename=@FullFileName,
@with='Move "PAL_COMPSN_P80_Data" to "D:\PALFA\DataBase\PAL\PAL.MDF"',
@with='Move "PAL_COMPSN_P80_Log" to "D:\PALFA\DataBase\PAL\PAL.LDF"',
@with='standby="D:\PALFA\DataBase\PAL\UNDO_PAL.DAT"'
执行完后就会在DB中出现一个备份/只读的PAL数据库。如果之前DB中存在一个可读写的PAL数据库，只需要更改语句为：
Declare @FullFileName Varchar(200)
Set @FullFileName='\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_FULL20080914.BAK'
EXEC master.dbo.xp_restore_database
@database='PAL',@Filename=@FullFileName,
@with='Move "PAL_Data" to "D:\PALFA\DataBase\PAL\PAL.MDF"',
@with='Move "PAL_Log" to " D:\PALFA\DataBase\PAL\PAL.LDF"',
@with='standby="D:\PALFA\DataBase\PAL\UNDO_PAL.DAT"',
@with='replace'
只是在结尾添加 ,@with='replace'这一句。
五、还原Diff差异备份
Declare @FullFileName Varchar(200)
Set @FullFileName='\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_DIFF20080919.BAK'
EXEC master.dbo.xp_restore_database
@database='PAL',@Filename=@FullFileName,
@with='Move "PAL_COMPSN_P80_Data" to "D:\PALFA\DataBase\PAL\PAL.MDF"',
@with='Move "PAL_COMPSN_P80_Log" to "D:\PALFA\DataBase\PAL\PAL.LDF"',
@with='standby="D:\PALFA\DataBase\PAL\UNDO_PAL.DAT"'
---@with='recovery'
@with='standby="D:\PALFA\DataBase\PAL\UNDO_PAL.DAT"'
替换为@with='recovery'代表还原后数据库可读写
六、还原Log日志备份
1)Litespeed还原Log
Declare @FileNum int
SET @FileNum=5
Declare @FullFileName Varchar(200)
Set @FullFileName= '\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_LOG20080918.TRN'
While @FileNum<=17
BEGIN
EXEC master.dbo.xp_restore_log
@database='PAL',@Filename=@FullFileName,
@filenumber=@FileNum,
@with='standby=" D:\PALFA\DataBase\PAL\UNDO_PAL.DAT"'
SET @FileNum=@FileNum+1
END
2)SQL还原Log
Declare @i int
Set @i=5
while @i<18
Begin
Restore log PAL from disk='\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_LOG20080918.TRN'
with STANDBY='D:\PALFA\DataBase\PAL\UNDO_PAL.DAT',file=@i
Set @i=@i+1
End
最后一个LOG
Restore log PAL from disk='\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_LOG20080918.TRN '
with recovery,file=18 ---最大的POSITION
这样还原后数据库可读写
七、Litespeed Point还原
现在要求还原到
Declare @FileNum int
SET @FileNum=18
Declare @FullFileName Varchar(200)
Set @FullFileName= '\\172.26.40.6\d$\PALBackUp\PAL_COMPSN_P80_LOG20080917.TRN'
EXEC master.dbo.xp_restore_log
@database='PAL_LORI',@Filename=@FullFileName,@WITH='RECOVERY',
@filenumber=@FileNum,
@with='STOPAT="2008-09-17 16:30:00.000"'
八,映射账户
在PAL db执行下面语句后就可以把之前UID为qmsuser和sdsuser赋之前相对应的权限。
sp_change_users_login 'Update_One','qmsuser','qmsuser'
sp_change_users_login 'Update_One','sdsuser','sdsuser'
