基本来说大体可以分为2类：在线备份和离线备份：
离线备份：
1.可以直接copy datafile and related files to your disk。
2.通过数据库带的备份工具或命令进行备份。
在线备份：
1.执行备份命令。
2.通过数据库带的备份工具进行备份。
像oracle还有一套数据库管理系统，可以通过网页浏览，然后进行对db的配置以及备份和恢复。
恢复：通过日志进行恢复！
1、备份位置
在SQL Server上，有多种备份位置可以选择，如本地磁盘，网络磁盘，远程地址，磁带等。
各种备份位置均有自己的优点和缺点。
2、逻辑备份设备
在SQL Server上，可以通过创建逻辑备份设备来完成备份。使用逻辑备份设备的好处是，当变更备份地址时，不需要更改备份脚本，只需要更改逻辑备份设备的定义即可。
创建逻辑备份设备的脚本如下：
SQL code
EXEC sp_adddumpdevice @devtype=’disk’,@logicalname=’MYBackup’,@physicalname=’D:\backup\mydb.bak’
删除备份设备的脚本：
SQL code
Sp_dropdevice @logicalname=’MYBackup’
上述脚本只是删除逻辑备份设备的定义，下述脚本将同时删除备份文件：
SQL code
Sp_dropdevice @logicalname=’MYBackup’,@devfile=’DELFILE’
使用逻辑备份设备的方法如下：
SQL code
Backup database mydb to MYBackup
当然，还可在逻辑备份设备上指定过期时间等备份属性，如：
SQL code
Backup database mydb to MYBackup WITH EXPIREDATE=’13/01/2010’
或：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7
3、备份集与存储集
每一份备份包含于一个备份集，而一个备份集包含于一个存储集。通过系统GUI进行备份时，SQL Server会自动指定备份集和存储集，目的则是为了简化管理。用T-SQL显示指定则用如下语法：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7,
NAME=’FULL’,
MEDIANAME=’ALLBackups’
NAMEs是指备份集名称，MEDIANAME是指存储集名称。
4、全备份
不管恢复模式是哪一个，所有的备份都必须要有一个全备份，特别是日志备份和差异备份，如果没有全备份的话，将无法进行恢复。
简单的全备份脚本如下所示，也可以通过维护计划来指定全备份：
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’
但需要注意的是，上述命令是将数据库备份附加到当前的存在的文件上，如果不存在则创建它，并不会覆盖原有文件。要覆盖同名的备份文件，需要指定INIT参数。
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’ WITH INIT
5、日志备份
在完全恢复模式或者大容量日志恢复模式下，日志备份不仅仅是恢复的需要，同时也是手工管理事务日志文件的一种方式。如果从不进行备份的话，在完全恢复模式或者大容量恢复模式下，事务日志将会持续增长，直至消耗完所在磁盘。
日志备份的脚本如下：
SQL code
BACKUP LOG mydb_log TO DISK=’D:\backup\mydb.trn’
需要养成使用.trn为日志备份的扩展名的习惯。
每个在数据库上的动作都会被安排一个Log Sequence Number (LSN)。如果需要还原到指定的时间点，需要有持续的LSN记录。也就是说，在完全恢复模式或者大容量日志模式下，一个不被打断的事务日志备份链是恢复数据库的基本要求。
6、差异备份
使用日志备份来恢复时，无疑是一个很慢的过程，特别是上一个全备份的历史比较悠久时。使用差异备份，便能缩短恢复时间。事实上，差异备份只是BACKUP DATABASE的一个选项，如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\backup\mydb.dif’ WITH DIFFERENTIAL,INIT
进行数据库恢复时，先恢复数据库全备份，再恢复数据库差异备份，最后才恢复日志备份。
差 异备份是与上一次全备份紧密相连的，不管期间有多少次日志备份和差异备份，差异备份还是会从上一次全备开始备份。因此，经常会遇到这样的一种情况，在生产 库上需要临时使用数据库时，便用BACKUP DATABASE … TO DISK=’..’进行了一个备份，下一次的差异备份便会以这回的全备为准，如果过后把这个临时全备删除掉后，后面的差异备份就没用了。
差异备份并不意味着磁盘空间肯定会少，这取决于实际情况。当期间大量 *** 作发生时，差异备份还是会变得很大。
7、错误检测
在备份过程中，备份进程会同时验证数据，或者校验不完整页（torn page），或者验证校验和(checksum)。要使用该功能，需要激活该选项。
不完整页检测(Torn-page dection)仅仅检查每一个页看是否已经写完成。如果发现一个页只有部分被写入，那么就将其标记为torn。
校验和验证(checksum validation)是一种新的页验证机制。它会为每个页添加一个值来表明该页实际的大小。虽然看起来是个代价很高影响性能的 *** 作，但事实上，它的效率非常高，与torn-page差不多。
备份进程在备份数据库时，会通过比较在数据库里的和随着备份页写入硬盘时这两个之间的值来进行验证。但是，这个验证并不是自动完成的，需要显示指定，在GUI页面上是个选项。如果通过T-SQL来备份的话，语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\data\mydb.bak’ WITH CHECKSUM
如果备份过程中，发现了错误，SQL Server会错误信息写入MSDB上的SUSPECT_PAGE表里面。同时，在默认情况下，备份行为会停止的（STOP_ON_ERROR），以便管理员排查错误。
但 备份过程中的校验和验证还有另外一个选项（CONTINUE_ON_ERROR）,也就是说，如果发现错误，备份过程并不会中断，而是将错误页信息记录在 MSDB..SUSPECT_PAGE上而已。需要注意的是，SUSPECT_PAGE表是有行限制的，最多只能达到1000行，如果达到了的话，备份同 样会失败。
激活校验和验证的话，很明显会影响备份的性能。但还是很有必要的。
8、安全备份
完全备份和日志备份语句还支持使用密码属性，如：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH PASSWORD=’mydb’
所指定的密码是很容易破解的。因此，如果确实需要对某些备份数据进行加密的话，可以将备份存放于加密的文件系统或者其它安全的存储设备上。
同时，SQL Server还提供了对真实列进行加密的功能。该加密功能是工业标准。
9、条带备份
有些情况下，单独一个硬盘无法存储一个完整的数据库备份时，可以将数据库备份分成多个部分存储在不同的磁盘上，这种备份方式成为条带备份。使用条带备份的优点很明确，就是能很好的利用空间，但如果某部分备份丢失或者损坏，那整个备份将无效。
其语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’,
DISK=’E:\mydb.bak’ WITH INIT,CHECKSUM,
CONTINUE_ON_ERROR
上述D盘和E盘上的备份是不可分割的。
10、镜像备份
与条带备份在多个磁盘上保留同一份备份不同的是，镜像备份是在不同磁盘上保留多份备份。其语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’
MIRROR TO DISK=’E:\mydb.bak’
WITH INIT,CHECKSUM,CONTINUE_ON_ERROR
在实际情况下，对日志备份采取镜像备份方式会比较合适。
11、COPY-ONLY 备份
在差异备份里曾提到过，差异备份是建立在上一个全备份的基础上的。因此如果在一个事先安排好的备份计划里，如果在全备份和差异备份之间再进行了一次全备份后，其差异备份会被打断，如果把临时全备份删除掉后，就产生了数据丢失。
在SQL SERVER 2005以后，SQL SERVER提供了一个选项copy-only.使用copy-only选项进行的全备份便不会打算原先的备份计划，语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’
WITH INIT,CHECKSUM,COPY_ONLY
三、文件和文件组备份
在SQL SERVER 7.0以后，SQL Server提供了filegroup的概念。文件组不仅仅提供了一个逻辑的存储地址，还允许将不同的表和索引放在不同的文件组上来提高性能和减少备份时间。
在数据存储概述上，提到了数据库有三类数据文件，一般来说，数据库至少需要两个（.mdf和.ldf）或者更多的文件。SQL Server不仅允许有多个文件存在(.ldf)，还允许多个文件组存在。
一个文件组可以有多个文件，每个文件需要仔细规划好初始大小及增量。
1、默认文件组
在创建对象时，如果未明确指定文件组，那么该对象将会存放在默认的文件组上。在默认情况下，默认的文件组是primary，但由于primary文件组不仅可以包含用户数据，同时还存储着数据库结构等技术信息，因此一般建议添加额外的一个文件组，并将其指定为默认文件组。
SQL Server只能有一个默认的文件组。
修改默认文件组的语句如下：
SQL code
ALTER DATABASE mydb MODIFY FILEGROUP mydb DEFAULT；
2、为对象指定文件组
当创建表或者索引时，不管是用户还是系统都需要将表或索引放在某个文件组上。如果在创建表或索引时指定文件组，那么表或索引将会存储在指定的文件组上，而不是默认的文件组。
其指定语句如下：
SQL code
CREATE TABLE test( [id] int,[notes] text) on mydbdata
也可以通过GUI创建表的页面来指定文件组。
3、将对象迁移至指定文件组
如果需要变更对象的存储位置，最简单的方式则是通过GUI属性页面来进行修改通过该方法，可以直观的看到对象的迁移过程。
同时也可以通过T-SQL来修改，例如：
SQL code
ALTER TABLE test drop constraint PK_test WITH (MOVE TO DATA)
4、备份数据文件
备份数据文件同样可以通过BACKUP DATABASE语句来实现。如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’ TO DISK=’E:\Backup\mydbdata.bak’
上述语句相当于数据文件级别的全备份，与数据库级别的备份类似，文件级别上的备份也有差异备份，当然前提是要有相对应的文件全备份。
差异备份的语句如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’
WITH DIFFERENTIAL
TO DISK=’E:\Backup\mydbdata_dif.bak’
5、备份文件组
与单独备份文件类似，也可以对文件组进行类似的备份 *** 作。备份文件组的方式也有两种，一种是通过GUI界面指定，一种则是通过T-SQL。
T-SQL的语句如下：
SQL code
BACKUP DATABASE mydb FILEGROUP=’PRIMARY’ TO DISK=’E:\Backup\mydbpri.bak’
6、不完全备份(partial backup)
在文件组备份上，不完全备份其实相当于完全备份，可以通过指定关键字READ_WRITE_FILEGROUPS来实现不完全备份。
语句如下：
SQL code
BACKUP DATABASE mydb READ_WRITE_FILEGROUPS TO DISK=’D:\mydb.bak’
在完整恢复模式或大容量日志恢复模式下，必须先备份活动事务日志（称为日志尾部），然后才能在SQLServerManagementStudio中还原数据库。有关详细信息，请参阅如何备份事务日志(SQLServerManagementStudio)。若要还原已加密的数据库，您必须有权访问用于加密数据库的证书或非对称密钥。如果没有证书或非对称密钥，数据库将无法还原。
认识数据库备份和事务日志备份
数据库备份与日志备份是数据库维护的日常工作，备份的目的是在于当数据库出现故障或者遭到破坏时可以根据备份的数据库及事务日志文件还原到最近的时间点将损失降到最低点。
数据库备份
数据库备份可以手动备份和语句备份
一.手动备份数据库
1.鼠标右键选择你要进行备份的数据库-任务-备份
可以在常规选项页面你可以选择备份类型是进行完整数据库备份还是差异数据库备份
2.点击添加选项，选择数据库文件的存放路径
注意文件名记得加后缀.bak,便于恢复时的查找
3.你还可以在选项页面是追加到现有的备份集，还是覆盖所有的现有备份集，还可以选择备份验证完整性（建议选择），还可以选择是否压缩备份等。
二.语句备份数据库
use master goBACKUP DATABASE [test] TO DISK = N'D:Microsoft sql serverMSSQL10.MSSQLSERVERMSSQLBackupest.bak' WITH NOFORMAT, NOINIT, NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO
数据库日志备份
首先需要注意，数据库日志的备份是基于数据库完整备份，也就是说你备份数据库日志之前你首先要先对数据库进行一次完整的备份，因为之间会涉及到坚持到检查点 lsn， 这也是本文接下来要讲的重点。
一.手动备份数据库日志
1.右键数据库-任务-备份-选择备份类型（事务日志）
2.点添加，添加日志文件备份存储路径
3.同数据库完整备份一样，你也可以选择覆盖现有备份集或者追加到现有备份集，这里现在覆盖现有备份集、验证完整性，然后确认备份
二.语句备份数据库事务日志
BACKUP LOG [test] TO DISK = N'D:est.trn' WITH NOFORMAT, INIT, NAME = N'test-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO
数据库还原
右键数据库-还原数据库-添加需要进行还原的数据库文件路径
在还原源选项中你可以选择‘源数据库’，‘源设备’。1.选择源数据库工具会自动显示该数据库之前的一些备份，然后直接选择需要还原的数据库备份集。
2.选择源设备点击后面的...，添加需要还原的数据库文件
2.点击确认还原数据库
数据库恢复
数据库恢复的前提是1.一个完整的数据库备份2.包含这个完整数据库备份的事务日志备份3.完整备份之间也可以存在数个差异备份
对于数据库维护空间始终是一个比较头疼的问题，特别是对于大型数据库而言，每天的日志文件增长是庞大的，很多数据库管理员会定时对数据库日志文件进行收缩，但是经常收缩会存在收缩完日志文件还是不能减少，这是因为存在很多活动的日志无法收缩可以用
DBCC LOGINFO('数据库名称')
我们看到
status=0的日志，代表已经备份到磁盘的日志文件；而
status=2的日志还没有备份。当我们收缩日志文件时，收缩掉的空
间其实就是
status=0的空间，如果日志物理文件无法减小，这里一
定能看到非常多status=2的记录
解决办法：1.可以分离要收缩的数据库，然后手动删除日志文件，然后附加数据库，数据库就会产生一个很小的日志文件（不推荐使用这种方法）
2.右键要出来的数据库选择“属性”-"选项"，将恢复模式改成"简单"，然后利用收缩工具可以讲日志文件收缩到很小，收缩完记得讲恢复模式改成"完整"
也可以用语句进行处理(dbname是你要进行收缩的数据库名，dbname_log是你要进行收缩的数据库的逻辑日志名称)
USE [master]
GOALTER DATABASE [dbname] SET recovery SIMPLE WITH NO_WAITGO
ALTER DATABASE [dbname] SET RECOVERY SIMPLE --简单模式
GO
USE [dbname]
GO
DBCC SHRINKFILE (N'dbname_log' , 11, TRUNCATEONLY)GO
USE [master]
GO
ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAITALTER DATABASE [dbname] SET RECOVERY FULL
对于第一种方法不赞同使用，首先对于数据库的分离与附加有时候会破坏数据库，造成数据库无法还原，还有就是对于在线数据库也不允许进行分离 *** 作。
对于第二种方法是slq2008收缩日志文件的一种方法，但是此方法也不能使用过于频繁，因为进行数据库恢复模式的更改会截断事务日志文件，这样的话当时利用事务日志文件进行恢复的时候检查点不能包含数据库文件，而且当你要对事务日志进行备份的时候会重新提示你需要对数据库进行完整备份。
举个例子：比如你昨天晚上进行了一次完整备份，然后同时你也进行了一次日志备份（提前日志未被截断），然后你每个小时进行过一次差异备份，最近的差异备份时间点是14点，如果此时数据库错误修改了数据，你可以立马备份一个日志文件将数据库恢复到日志备份开始到日志备份终点前的任意时间点 。
如果此时你进行了修改数据库模式，截断日志进行了收缩，那么你的数据只能恢复到昨天晚上备份的那个日志备份时间前的任意时间点，也就是今天所做的数据库更改无法再恢复了，因为日志文件已经被截断了，不知道这样解释是否明白
因为日志文件的检查点（lsn）是连续的，每一次日志备份都是在上一次备份的基础上lsn往后增加的，lsn的范围也包括了数据库文件的lsn，也只有日志文件的lsn包括了数据库文件的lsn,才能将数据库文件进行回滚。
上图中总共有三个备份文件，一个完整备份、一个差异备份、一个日志备份，大家可以注意观察完整备份的第一个lsn与最后一个lsn,和检查点
第二个差异备份文件的的第一个lsn与最后一个lsn,和检查点，最后的日志备份的第一个lsn和最后一个lsn包含了前面两个备份文件的lsn,这种情况数据库就可以恢复到日志文件备份前的任意时间点，如果日志文件没有包含数据库文件的最后一个lsn也就无法恢复了。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）