--完整備份 Backup Database NorthwindCS To disk='G:\Backup\NorthwindCS_Full_20070908.bak' --差異備份 Backup Database NorthwindCS To disk='G:\Backup\NorthwindCS_Diff_20070908.bak' With Differential --日志備份，默認(rèn)截?cái)嗳罩?Backup Log NorthwindCS To disk='G:\Backup\NorthwindCS_Log_20070908.bak' --日志備份,，不截?cái)嗳罩?Backup Log NorthwindCS To disk='G:\Backup\NorthwindCS_Log_20070908.bak' With No_Truncate --截?cái)嗳罩静槐Ａ?Backup Log NorthwindCS With No_Log --或者 Backup Log NorthwindCS With Truncate_Only --截?cái)嘀笕罩疚募粫?huì)變小 --有必要可以進(jìn)行收縮 --文件備份 Exec Sp_Helpdb NorthwindCS --查看數(shù)據(jù)文件 Backup Database NorthwindCS File='NorthwindCS' --數(shù)據(jù)文件的邏輯名 To disk='G:\Backup\NorthwindCS_File_20070908.bak' --文件組備份 Exec Sp_Helpdb NorthwindCS --查看數(shù)據(jù)文件 Backup Database NorthwindCS FileGroup='Primary' --數(shù)據(jù)文件的邏輯名 To disk='G:\Backup\NorthwindCS_FileGroup_20070908.bak' With init --分割備份到多個(gè)目標(biāo) --恢復(fù)的時(shí)候不允許丟失任何一個(gè)目標(biāo) Backup Database NorthwindCS To disk='G:\Backup\NorthwindCS_Full_1.bak' ,disk='G:\Backup\NorthwindCS_Full_2.bak' --鏡像備份 --每個(gè)目標(biāo)都是相同的 Backup Database NorthwindCS To disk='G:\Backup\NorthwindCS_Mirror_1.bak' Mirror To disk='G:\Backup\NorthwindCS_Mirror_2.bak' With Format --第一次做鏡像備份的時(shí)候格式化目標(biāo) --鏡像備份到本地和遠(yuǎn)程 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 --每天生成一個(gè)備份文件 Declare @Path Nvarchar(2000) Set @Path ='G:\Backup\NorthwindCS_Full_' +Convert(Nvarchar,Getdate(),112)+'.bak' Backup Database NorthwindCS To disk=@Path --從NoRecovery或者 --Standby模式恢復(fù)數(shù)據(jù)庫(kù)為可用 Restore Database NorthwindCS_Bak With Recovery --查看目標(biāo)備份中的備份集 Restore HeaderOnly From Disk ='G:\Backup\NorthwindCS_Full_20070908.bak' --查看目標(biāo)備份的第一個(gè)備份集的信息 Restore FileListOnly From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak' With File=1 --查看目標(biāo)備份的卷標(biāo) Restore LabelOnly From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak' --備份設(shè)置密碼保護(hù)備份 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' =================================== sql 2000 一、指定邏輯備份設(shè)備 backup databse accounting to accounting_bak 二,、或指定物理備份設(shè)備 backup database accouning to disk='d:\backup\accouning\full.bak' 三、執(zhí)行無(wú)日志操作后的命令 backup log with truncate_only writetext 或 backup log with NO_log updatetext select ... into.... 四,、設(shè)置數(shù)據(jù)庫(kù)的還原模型 1,、完全模型 alter database northwind set recovery full 2、簡(jiǎn)單模型 alter database norhtwind set recovery simple 3,、大容量日志記錄bulk_logged模型 alter database northwind set recovery bulk_logged 五、添加備份設(shè)備 sp_addumpdevice 'disk','NWdevice','d:\backup\mydevice.bak' 六,、刪除一個(gè)備份設(shè)備 sp_dropdevice 'nwdevice','delfile' /*如果加上delfile就是直接把文件也刪除掉*/ 七,、差異備份的語(yǔ)法 backup database northwind to nwdevice with differential 八、事務(wù)日志備份 backup log company to NWdevice with no_truncate（不清空原有日志） 九,、文件和文件組備份及還原 backup database commpany filegroup='primary' to NWdevice 文件組的名稱是"primary" 十,、數(shù)據(jù)恢復(fù)(從硬盤(pán)恢復(fù)) restore database company from disk='d:\backup\company.bak' 還可繼續(xù)在后面加參數(shù)： with nonrecovery recovery replace 注： (1)recovery:最后一次一次恢復(fù)時(shí)使用 (2)replace：是否替換己有文件 http://synox2008.blog.163.com/blog/static/681984002009112294738782/