SQLServer备份原理以及备份脚本推荐

官方关于备份的表述：https://docs.microsoft.com/zh-cn/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-2016

declare @path nvarchar(256) 
set @path = 'D:\DBCenter\Backup_DataBase\'+'DB_' + FORMAT(GETDATE(),'yyyyMMdd') + '.bak'    
IF FORMAT(GETDATE(),'dd')='01'    
BEGIN    
backup database [DB] to disk = @path WITH MEDIANAME = 'SQLServerBackups',NAME = 'Full Backup of XYJD';    
END    
ELSE    
BEGIN    
set @path = 'D:\DBCenter\Backup_DataBase\'+'DB_' + FORMAT(GETDATE(),'yyyyMM01') + '.bak'    
BACKUP LOG [DB] TO DISK = @path    
WITH NOFORMAT, NOINIT, NAME = N'XYHotel-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10    
END

一、full模式

处于该模式的数据库，不会主动截断log，只有备份Log之后，才可以截断Log。否则Log文件会一直增大，只到撑爆硬盘，因此需要部署一个job定时备份Log，full的好处是可以坐基于时间点的恢复，最大限度的保证数据不丢失。缺点是需要dba维护Log。

二、simple模式

处于该模式的数据库，sqlserver会在每次checkpoint或backup之后自动截断Log，也就是丢弃所有的inactive log records，仅保留用于实例启动时自动发生的实例恢复所需的少量Log，这样做的好处是log文件非常小，不需要DBA去维护，坏处是一旦数据库出现异常，需要恢复时，最多只能恢复到上一次的备份，无法恢复到最近可用状态，因为Log丢失了。

三、bulk-logged模式（大容量日志恢复模式）

与完整恢复模式相比，大容量日志恢复模式只对大容量操作进行最小记录，通常buld操作会产生大量的Log，对sqlserver性能有较大的影响，bulk-logged模式的作用就在于降低这种性能影响，并防止log文件过分增长，但是它的问题是无法point-in-time恢复到包含bulk-logged record的这段时间。

Bulk-logged模式的最佳实践方案是在做bulk操作之前切换到bulk-logged，在bulk操作结束之后马上切换回full模式。

大容量操作：

1 bcp and bulk insert

2 select into

3 create/drop/rebuild index

Master
Master数据库保存有放在SQLSERVER实体上的所有数据库，它还是将引擎固定起来的粘合剂。由于如果不使用主数据库，SQLSERVER就不能启动，所以你必须要小心地管理好这个数据库。

Model
Model是一个用来在实体上创建新用户数据库的模版数据库。你可以把任何存储过程、视图、用户等放在模型数据库里，这样在创建新数据库的时候，新数据库就会包含你放在模型数据库里的所有对象了。

Tempdb
正如其名字所提示的，tempdb存有临时对象，例如全局和本地临时表格和存储过程。
这个数据库在SQLSERVER每次重启的时候都会被重新创建，而其中包含的对象是依据模型数据库里定义的对象被创建的。除了这些对象，tempdb还存有其他对象，例如表格变量、来自表格值函数的结果集，以及临时表格变量。由于tempdb会保留SQLSERVER实体上所有数据库的这些对象类型，所以对数据库进行优化配置是非常重要的。

Distribution
当你的SQLSERVER实体被配置为复制分发SERVER时，这个数据库就会被添加到你的系统里。在默认情况下，数据库的名字就是distribution，但是你可以更改它的名字。这个数据库用来保存历史和快照、合并和事务复制等的元数据

Msdb
Msdb数据库用来保存于数据库备份、SQL Agent信息、DTS程序包、SQLSERVER任务等信息，以及诸如日志转移这样的复制信息。

恢复

在简单恢复模式下，请考虑将差异备份安排在完整数据库备份之间。差异备份只能捕获自上次完整数据库备份之后的更改。