|
本文为你详解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 database master from masterbak restore database msdb from disk='e:\bak\msdb.bak' MASTER还原后，SQL中用户数据库的信息也会恢复。 （6）如果MASTER没有备份，则需要用sp_attach_db命令将用户数据库附加到新的MASTER数据库中。 ---------------------------------------------------------------------- 5、自动化备份实现（要将sqlserveragent服务设置为自动启动，并启动该服务） 我们日常使用的MOSS进行公司部门站点的信息管理与收发,后台使用MS SQL2005. 计划采用完全备份,差异备份和日志备份来实现MOSS数据的日常备份和管理. 计划如下： 采用完全恢复模型。 备份设备：disk为主，可以用本地磁盘或网络磁盘备份。 备份方法：完全备份+差异备份+事务日志备份。 备份日程：完全备份每天进行一次，差异备份每四个小时一次，事务日志备份每三十分钟一次。备份网络磁盘仅保留近一周的备份文件。 备份文件命名： 完全备份：数据库实例名称+“_full.bak” 差异备份：数据库实例名称+“_diff_”+hh+”.b” 事务日志备份：数据库实例名称+“_log_+hhmm 备份路径：网络磁盘\YYMMDD\ 下文以eip数据库实例为例来说明操作步骤。 每天检查并删除过时的备份文件 --Function:full backup database --1 Variable declaration declare @DbPath varchar(500) declare @DbName sysname declare @FileName varchar(500) --2 Initialize variables set @DbName = 'wsseip' --custom set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\' set @FileName = @DbPath +@DbName+ '_full.bak' --3 Net connect and create file exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup' EXEC master.dbo.xp_create_subdir @DbPath --4 Create backup backup database EIP to disk=@FileName with init go 每天做一次完全备份 --Function:del backup database --1 Variable declaration declare @DbPath varchar(500) declare @DbName sysname declare @FileName varchar(500) declare @CmdShell varchar(500) --2 Initialize variables set @DbName = 'wsseip' --custom set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),dateadd(day,-5,getdate()),112) set @CmdShell ='rd /S /Q ' + @DbPath --3 Net connect and create file exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup' --4 Del log backup exec master..xp_cmdshell @CmdShell --删除5天前的备份，也就是只保留5个最新备份 go 每4个小时做一次差异备份 --Function:diff backup database --1 Variable declaration declare @DbPath varchar(500) declare @DbName sysname declare @FileName varchar(500) --2 Initialize variables set @DbName = 'wsseip' --custom set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\' set @FileName = @DbPath +@DbName+ '_diff_'+cast(datepart(hour,getdate()) as varchar)+'.bak' --3 Net connect and create file exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup' EXEC master.dbo.xp_create_subdir @DbPath --4 Create diff backup backup database EIP to disk=@FileName with differential --差异备份数据库 go 每30分钟做一次事物日志备份 --Function:diff backup database --1 Variable declaration declare @DbPath varchar(500) declare @DbName sysname declare @FileName varchar(500) --2 Initialize variables set @DbName = 'wsseip' --custom set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\' set @FileName = @DbPath +@DbName+ '_log_'+cast(datepart(hour,getdate()) as varchar)+cast(datepart(minute,getdate()) as varchar) --3 Net connect and create file exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup' EXEC master.dbo.xp_create_subdir @DbPath --4 Create log backup ALTER DATABASE eip SET RECOVERY FULL backup log eip to disk=@FileName --日志备份数据库 go (责任编辑：admin) |