SQL数据库备份

--完整备份

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 = '数据库自动通知' ;