--完整备份
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS.bak'
--明确标识数据库、日志和备份类型
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Full.bak'
--差异备份
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Diff.bak'
With Differential
--事务日志备份
Backup Log NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Log.bak'
--1个文件可以存放多次备份，默认是追加
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Full.bak'
--覆盖现有的文件
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Full.bak'
With init
--截断事务日志不备份
Backup Log NorthwindCS
With No_log
--或者
Backup Log NorthwindCS
With Truncate_only
--查询数据库的文件
Use NorthwindCS
Go
Exec sp_helpfile
--收缩指定的文件
Dbcc ShrinkFile(NorthwindCS_log,2048)
--备份日志但是不截断
Backup Log NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Log.bak'
With No_Truncate
--仅复制备份，不影响的备份序列
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_FT.bak'
With Copy_only
--尾部日志备份,备份完成后数据库不再提供服务
Use Master
Go
Backup Log NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Tail.bak'
With NoRecovery
--恢复“正在还原……”状态的数据库为可用
Restore Database NorthwindCS
With Recovery
--设置备份的密码
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Full.bak'
With password='ccadmin@520'
--日期的处理
Select getdate()
Select Convert(char(10),getdate())
Select Convert(char(10),getdate(),120)
Select Convert(char(10),getdate(),112)
--执行相同的语句，每天生成一个独立的文件
Declare @Path varchar(500)
Set @Path = 'D:\Backup\NorthwindCS_'
+ Convert(char(8),getdate(),112)
+'_Full.bak'
Backup Database NorthwindCS
To Disk=@Path
--分割备份,将1个备份分割成为多个文件
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Part1.bak'
,Disk='D:\Backup\NorthwindCS_20110721_Part2.bak'
--镜像备份,每个目标文件是相同的
Backup Database NorthwindCS
To Disk='D:\Backup\NorthwindCS_20110721_Mirror1.bak'
Mirror to
Disk='D:\Backup\NorthwindCS_20110721_Mirror2.bak'
With Format
--------------------------
--如何备份到远程服务器,同名同密码或者域用户启动
--------------------------
Backup Database NorthwindCS
To Disk='\\192.168.1.100\Backup$\NorthwindCS_20110721_Remote.bak'
--启用xp_cmdshell
Exec sp_configure
Exec sp_configure 'show advanced options',1
Reconfigure With override
Exec sp_configure 'xp_cmdshell',1
Reconfigure With override
-----------------------------
--不同名和密码的远程备份
-----------------------------
--建立远程的连接
Exec xp_cmdshell
'net use \\192.168.1.100\Backup$ /USER:sqlsa password'
Backup Database NorthwindCS
To Disk='\\192.168.1.100\Backup$\NorthwindCS_20110721_Remote.bak'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = '15889580578@139.com;xsfwxh@163.com',
@body = '数据库备份成功',
@subject = '数据库自动通知' ;