/************ 一、数据库备份 ************/

--完整备份默认追加到现有的文件

backup database DBXS To disk='d:\backup\DBXS_full.bak'

--完整备份，覆盖现有的文件

Backup database DBXS To disk='d:\backup\DBXS_full.bak' With init

--差异备份（上次一完整备份以来改变的数据页）

backup database DBXS To Disk='d:\backup\DBXS_diff.bak' with differential

--事物日志备份，会自动截断日志(默认会截断日志)

backup log DBXS To Disk='d:\backup\DBXS_log'

--事物日志备份，不截断日志(默认会截断日志)

backup log DBXS To Disk='d:\backup\DBXS_log' With No_Truncate

--不备份直接截断日志，在SQL SERVER2008中不再支持。

backup log DBXS With No_Log

backup log DBXS With Tuancate_only

--SQL SERVER 2008 替代的截断日志方法

exec sp_helpdb DBXS

use DBXS

alter database DBXS set Recovery Simple

dbcc shrinkfile('DBXS_log')

alter database DBXS set Recovery full

--超大型数据库的文件和文件组备份

Exec sp_helpdb DBXS

backup database DBXS File='DBXS_Current'

to disk='d:\backup\DBXS_Full.bak'

backup database DBXS FileGroup='Current'

to disk='d:\backup\DBXS_FG.bak'

--仅复制备份，不影响现有的备份序列

backup database DBXS To disk='d:\backup\DBXS_Full.bak' With Copy_only

--尾部日志备份,备份完成后数据库不再提供访问

use master

backup log DBXS to disk='d:\backup\DBXS_taillog.bak' With NoRecovery

--回复数据库提供访问

Restore database DBXS with Recovery

--分割备份到多个目标文件

backup database DBXS

to disk='d:\backup\DBXS_part1.bak',disk='d:\backup\DBXS_part2.bak'

--镜像备份，需要加入With Format

backup database DBXS

to disk='d:\backup\DBXS_Mirror1.bak'

Mirror to disk='d:\backup\DBXS_Mirror2.bak'--Mirror镜像

With Format

--备份到远程服务器

--使用SQL SERVER 的服务启动账号访问远程共享可写文件夹

backup database DBXS to disk='\\192.168.3.20\backup\DBXS.bak'

--备份到远程服务器,指定访问远程服务器的账号和密码

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.10.101 /user:administrator password'

backup database DBXS to disk='\\192.168.10.101\backup\DBXS.bak'

EXEC sp_configure 'xp_cmdshell', 0

Reconfigure with override

EXEC sp_configure 'show advanced options', 0

Reconfigure with override

--压缩备份

Backup Database AdventureWorks To disk='d:\backup\DBXS_cp.bak' With Compression

--启动默认压缩备份

EXEC sp_configure 'backup compression default','' RECONFIGURE WITH OVERRIDE

/************ 二、数据库还原 ************/

--断开所有用户链接，并回滚所有事务

alter Database DBXS set Offline with RollBack Immediate

alter Database DBXS set online

--还原数据库（完整备份）

Restore Database DBXS From disk='d:\backup\DBXS_full.bak' with Replace

--还原数据库（差异备份）

Restore Database DBXS from disk='d:\backup\DBXS_full.bak' with Replace,noRecovery

Restore Database DBXS from disk='d:\backup\DBXS_diff.bak' with Recovery