SQL server 数据库备份还原Sql

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

--完整备份默认追加到现有的文件
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','1' 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