笔记86 查看目标备份中的备份集和各种备份、还原脚本

  1 --查看目标备份中的备份集和各种备份、还原脚本
  2 Restore HEADERONLY From Disk ='D:\GPOSDB_backup_201207160058.bak'
  3 
  4 
  5 -----------------------各种备份、还原脚本------------------------------------------
  6 --完整备份
  7 Backup DATABASE GPOSDB
  8 To disk='D:\GPOSDB_backup_201207160152.bak'
  9 
 10 --差异备份
 11 Backup Database NorthwindCS
 12 To disk='G:\Backup\NorthwindCS_Diff_20070908.bak'
 13 With Differential
 14 
 15 --日志备份，默认截断日志
 16 Backup Log NorthwindCS
 17 To disk='G:\Backup\NorthwindCS_Log_20070908.bak'
 18 
 19 --日志备份，不截断日志
 20 Backup Log NorthwindCS
 21 To disk='G:\Backup\NorthwindCS_Log_20070908.bak'
 22 With No_Truncate
 23 
 24 --截断日志不保留
 25 Backup Log NorthwindCS
 26 With No_Log
 27 
 28 --或者
 29 Backup Log NorthwindCS
 30 With Truncate_Only
 31 --截断之后日志文件不会变小
 32 --有必要可以进行收缩
 33 
 34 --文件备份
 35 Exec Sp_Helpdb NorthwindCS --查看数据文件
 36 Backup Database NorthwindCS
 37 File='NorthwindCS'   --数据文件的逻辑名
 38 To disk='G:\Backup\NorthwindCS_File_20070908.bak'
 39 
 40 --文件组备份
 41 Exec Sp_Helpdb NorthwindCS --查看数据文件
 42 Backup Database NorthwindCS
 43 FileGroup='Primary'   --数据文件的逻辑名
 44 To disk='G:\Backup\NorthwindCS_FileGroup_20070908.bak'
 45 With init
 46 
 47 --分割备份到多个目标
 48 --恢复的时候不允许丢失任何一个目标
 49 Backup Database NorthwindCS
 50 To disk='G:\Backup\NorthwindCS_Full_1.bak'
 51      ,disk='G:\Backup\NorthwindCS_Full_2.bak'
 52 
 53 --镜像备份
 54 --每个目标都是相同的
 55 Backup Database NorthwindCS
 56 To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
 57 Mirror
 58 To disk='G:\Backup\NorthwindCS_Mirror_2.bak'
 59 With Format --第一次做镜像备份的时候格式化目标
 60 
 61 --镜像备份到本地和远程
 62 Backup Database NorthwindCS
 63 To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
 64 Mirror
 65 To disk='\\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak'
 66 With Format
 67 
 68 --每天生成一个备份文件
 69 Declare @Path Nvarchar(2000)
 70 Set @Path ='G:\Backup\NorthwindCS_Full_'
 71 +Convert(Nvarchar,Getdate(),112)+'.bak'
 72 
 73 Backup Database NorthwindCS
 74 To disk=@Path
 75 
 76 
 77 --从NoRecovery或者
 78 --Standby模式恢复数据库为可用
 79 Restore Database NorthwindCS_Bak
 80 With Recovery
 81 
 82 --查看目标备份中的备份集
 83 Restore HeaderOnly
 84 From Disk ='D:\GPOSDB_backup_201207160058.bak'
 85 
 86 --查看目标备份的第一个备份集的信息 下面这个sql语句才看到DifferentialBaseGUID的值
 87 Restore FileListOnly
 88 From Disk ='D:\GPOSDB_backup_201207160058.bak'
 89 With File=1
 90 
 91 --查看目标备份的卷标
 92 Restore LabelOnly
 93 From Disk ='D:\GPOSDB_backup_201207160058.bak'
 94 
 95 --备份设置密码保护备份
 96 Backup Database NorthwindCS
 97 To disk='G:\Backup\NorthwindCS_Full_20070908.bak'
 98 With Password = '123',init
 99 
100 Restore Database NorthwindCS
101 From disk='G:\Backup\NorthwindCS_Full_20070908.bak'
102 With Password = '123'