SQL还原

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 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