sqlserver 数据库备份

/********************逻辑备份(使用备份设备进行备份):方便管理和恢复**************/
--  创建逻辑备份设备
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', 
@logicalname = N'PartDB', 
@physicalname = N'I:\sqlserver\backupDevice\PartDB.bak'
GO

--  删除备份设备,只是删除逻辑备份设备的定义
Sp_dropdevice @logicalname = 'PartDB'
GO
--  删除备份设备,同时删除备份文件
Sp_dropdevice @logicalname='PartDB',@delfile='I:\sqlserver\backupDevice\PartDB.bak'
GO
 
--  逻辑备份,指定过期时间或保留天数
Backup database PartDB to PartDB WITH EXPIREDATE = '11/11/2012'
Backup database PartDB to PartDB WITH RETAINDAYS = 7




/******************************DISK磁盘备份*****************************/
-- 数据库完全备份
BACKUP DATABASE PartDB to DISK='I:\sqlserver\diskbackup\Part.bak' --附加或创建
WITH INIT --覆盖同名文件

-- 日志备份
BACKUP LOG PartDB TO DISK='I:\sqlserver\diskbackup\Part_log.trn'


-- 差异备份:总是从上一次全备开始备份
BACKUP DATABASE PartDB TO DISK='I:\sqlserver\diskbackup\Part_dif1.bak'
WITH DIFFERENTIAL,INIT

--数据库恢复时,先恢复数据库全备份，再恢复数据库差异备份，最后恢复日志备份


--错误检测
/*
在备份过程中，备份进程会同时验证数据，或者校验不完整页（torn page），或者验证校验和(checksum)。
要使用该功能，需要激活该选项。


不完整页检测(Torn-page dection)仅仅检查每一个页看是否已经写完成。
如果发现一个页只有部分被写入，那么就将其标记为torn。  


校验和验证(checksum validation)是一种新的页验证机制。它会为每个页添加一个值来表明该页实际的大小。
虽然看起来是个代价很高影响性能的操作，但事实上，它的效率非常高，与torn-page差不多。


如果备份过程中，发现了错误，SQL Server会错误信息写入MSDB上的SUSPECT_PAGE表里面。
同时，在默认情况下，备份行为会停止的（STOP_ON_ERROR），以便管理员排查错误。


但 备份过程中的校验和验证还有另外一个选项（CONTINUE_ON_ERROR）.
也就是说，如果发现错误，备份过程并不会中断，而是将错误页信息记录在 MSDB..SUSPECT_PAGE上而已。
需要注意的是，SUSPECT_PAGE表是有行限制的，最多只能达到1000行，如果达到了的话，备份同 样会失败。

*/
-- 数据库备份时校验
BACKUP DATABASE PartDB TO DISK='I:\sqlserver\diskbackup\Part.bak' WITH CHECKSU


-- 数据库备份时设置密码
BACKUP DATABASE  PartDB TO DISK='I:\sqlserver\diskbackup\Part.bak'  WITH PASSWORD='123456'


-- 条带备份,数据库分成多部分进行分布备份,不方便管理(恢复时都需要)
 BACKUP DATABASE PartDB TO 
 DISK='I:\sqlserver\diskbackup\Part.bak',
 DISK='J:\sqlserver\diskbackup\PartDB\Part.bak' 
 WITH INIT,CHECKSUM,CONTINUE_ON_ERROR --校验:错误时继续(STOP_ON_ERROR:错误则停止备份)


-- 镜像备份,在不同磁盘上保留(多份)同样的备份(恢复时只要一份)
BACKUP DATABASE PartDB 
TO DISK='I:\sqlserver\diskbackup\Part.bak'
MIRROR 
TO DISK='J:\sqlserver\diskbackup\PartDB\Part.bak' 
WITH INIT,CHECKSUM,CONTINUE_ON_ERROR  


-- COPY-ONLY 备份(不会打乱备份计划,如有差异备份时进行全备份后仍继续)
BACKUP DATABASE PartDB 
TO DISK='I:\sqlserver\diskbackup\Part.bak'
WITH INIT,CHECKSUM,COPY_ONLY




/***************************文件和文件组备份****************************/
/*
SQL Server数据库有三类数据文件,一般来说，数据库至少需要这两个(.mdf和.ldf)或者更多的文件。
SQL Server不仅允许有多个文件存在(.ldf)
SQL Server中一个文件可有多个文件，一个文件组对应一个或多个文件组的物理地址.
*/


-- 设置默认文件组
ALTER DATABASE PartDB MODIFY FILEGROUP filegroup01 DEFAULT


-- 创建对象时指定文件组
CREATE TABLE test(id int,name varchar) on filegroup02  


-- 迁移对象到指定的文件组
ALTER TABLE test drop constraint PK_test WITH (MOVE TO filegroup01)


-- 备份文件(相当文件的一次全备份)
BACKUP DATABASE PartDB 
FILE='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PartDB_second.ndf'
TO DISK='I:\sqlserver\diskbackup\PartDB_second.bak'


-- 备份文件(差异备份)
BACKUP DATABASE PartDB 
FILE='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PartDB_second.ndf' 
TO DISK='I:\sqlserver\diskbackup\PartDB_second_diff.bak'
WITH DIFFERENTIAL 


-- 备份文件组(备份其逻辑名)
BACKUP DATABASE PartDB 
FILEGROUP='PRIMARY' 
TO DISK='I:\sqlserver\diskbackup\PartDB_PRIMARY.bak'


-- 不完全备份(一个文件组设置了只读，而这只读的文件组又需要进行一次备份)
BACKUP DATABASE PartDB 
READ_WRITE_FILEGROUPS 
TO DISK='I:\sqlserver\diskbackup\Part.bak'