SQL 备份语句

--完整备份      


       --完整备份        
 Backup Database NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Full_20070908.bak'        

 --差异备份        
 Backup Database NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Diff_20070908.bak'        
 With Differential        

 --日志备份，默认截断日志        
 Backup Log NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Log_20070908.bak'        

 --日志备份，不截断日志        
 Backup Log NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Log_20070908.bak'        
 With No_Truncate        

 --截断日志不保留        
 Backup Log NorthwindCS        
 With No_Log        

 --或者        
 Backup Log NorthwindCS        
 With Truncate_Only        
 --截断之后日志文件不会变小        
 --有必要可以进行收缩        

 --文件备份        
 Exec Sp_Helpdb NorthwindCS --查看数据文件        
 Backup Database NorthwindCS        
 File='NorthwindCS' --数据文件的逻辑名        
 To disk='G:/Backup/NorthwindCS_File_20070908.bak'        

 --文件组备份        
 Exec Sp_Helpdb NorthwindCS --查看数据文件        
 Backup Database NorthwindCS        
 FileGroup='Primary' --数据文件的逻辑名        
 To disk='G:/Backup/NorthwindCS_FileGroup_20070908.bak'        
 With init        

 --分割备份到多个目标        
 --恢复的时候不允许丢失任何一个目标        
 Backup Database NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Full_1.bak'        
 ,disk='G:/Backup/NorthwindCS_Full_2.bak'        

 --镜像备份        
 --每个目标都是相同的        
 Backup Database NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Mirror_1.bak'        
 Mirror        
 To disk='G:/Backup/NorthwindCS_Mirror_2.bak'        
 With Format --第一次做镜像备份的时候格式化目标        

 --镜像备份到本地和远程        
 Backup Database NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Mirror_1.bak'        
 Mirror        
 To disk='//192.168.1.200/Backup/NorthwindCS_Mirror_2.bak'        
 With Format        

 --每天生成一个备份文件        
 Declare @Path Nvarchar(2000)        
 Set @Path ='G:/Backup/NorthwindCS_Full_'        
 +Convert(Nvarchar,Getdate(),112)+'.bak'        

 Backup Database NorthwindCS        
 To http://www.hack58.net/Article/html/3/7/2008/mailtdisk=@Path        

 --从NoRecovery或者        
 --Standby模式恢复数据库为可用        
 Restore Database NorthwindCS_Bak        
 With Recovery        

 --查看目标备份中的备份集        
 Restore HeaderOnly        
 From Disk ='G:/Backup/NorthwindCS_Full_20070908.bak'        

 --查看目标备份的第一个备份集的信息        
 Restore FileListOnly        
 From Disk ='G:/Backup/NorthwindCS_Full_20070908_2.bak'        
 With File=1        

 --查看目标备份的卷标        
 Restore LabelOnly        
 From Disk ='G:/Backup/NorthwindCS_Full_20070908_2.bak'        

 --备份设置密码保护备份        
 Backup Database NorthwindCS        
 To disk='G:/Backup/NorthwindCS_Full_20070908.bak'        
 With Password = '123',init        

 Restore Database NorthwindCS        
 From disk='G:/Backup/NorthwindCS_Full_20070908.bak'        
 With Password = '123'