SQL还原======================================================================1、验证备份------------------------------------------------------------restore headeronly from bak3restore filelistonly from bak3 with file=1restore labelonly from bak3restore verifyonly from bak3----------------------------------------------------------------------2、从备份中还原-------------------------------------------------------------------------restore headeronly from bak1restore 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,norecoveryrestore log d3 from bak3 with file=2,norecoveryrestore log d3 from bak3 with file=3,norecoveryrestore log d3 from bak3 with file=4,norecoveryrestore 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,norecoveryrestore log d3 from bak3 with file=3,norecoveryrestore 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,norecoveryrestore log d5 from bak5 with file=7,recovery----------------------------------------------------------------------restore headeronly from bak6 --还原文件备份restore database d5 file='d5_data3' from bak6 with file=6,norecoveryrestore log d5 from bak6 with file=7,norecoveryrestore 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)gobackupdatabase d6 to bak6 with initdrop database d6restore database d6 from bak6with 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 d6gocreate database d6on primary(filename='e:/data/d6/d6_data.mdf')for attachgo----------------------------------------------------------------------4、恢复损坏的系统数据库----------------------------------------------------------------------1）先备份MASTER、MSDB2）停止SQL服务，将MASTER数据库文件删除或者重命名。这样，SQL服务将不能启动。3）系统数据库的还原-----------------------------------------------（1）如果SQL服务还能启动，则从备份中恢复系统数据库。（2）如果SQL服务不能启动，则需要重建系统数据库。使用SQL文件夹TOOLS/BINN目录下的Rebuildm.exe重建master数据库。（3）创建备份设备，指向以前的备份设备。（4）以单用户模式启动SQLcd programe files/microsoft sql server/mssql/binnsqlservr.exe -c -m（5）进查询分析器，从备份中恢复master数据库。restore database master from masterbakrestore 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 declarationdeclare @DbPath varchar(500)declare @DbName sysnamedeclare @FileName varchar(500)--2 Initialize variablesset @DbName = 'wsseip' --customset @DbPath='//172.22.8.121/D$/dbbackup/'+@DbName+'/'+convert(varchar(10),getdate(),112) +'/'set @FileName = @DbPath +@DbName+ '_full.bak'--3 Net connect and create fileexec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'EXEC master.dbo.xp_create_subdir @DbPath--4 Create backupbackup database EIP to disk=@FileName with init go 每天做一次完全备份--Function:del backup database --1 Variable declarationdeclare @DbPath varchar(500)declare @DbName sysnamedeclare @FileName varchar(500)declare @CmdShell varchar(500)--2 Initialize variablesset @DbName = 'wsseip' --customset @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 fileexec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'--4 Del log backupexec master..xp_cmdshell @CmdShell --删除5天前的备份，也就是只保留5个最新备份go 每4个小时做一次差异备份--Function:diff backup database --1 Variable declarationdeclare @DbPath varchar(500)declare @DbName sysnamedeclare @FileName varchar(500)--2 Initialize variablesset @DbName = 'wsseip' --customset @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 fileexec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'EXEC master.dbo.xp_create_subdir @DbPath--4 Create diff backupbackup database EIP to disk=@FileName with differential --差异备份数据库go 每30分钟做一次事物日志备份--Function:diff backup database --1 Variable declarationdeclare @DbPath varchar(500)declare @DbName sysnamedeclare @FileName varchar(500)--2 Initialize variablesset @DbName = 'wsseip' --customset @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 fileexec master.dbo.xp_cmdshell 'net use //172.22.8.121/D$/dbbackup'EXEC master.dbo.xp_create_subdir @DbPath--4 Create log backupALTER DATABASE eip SET RECOVERY FULL backup log eip to disk=@FileName --日志备份数据库go
