sqlserver数据库备份方案

1、sql server数据库备份方案为了保证sql server数据的安全，数据库管理员应定期备份数据库，在不同情 况下应采用不同备份数据库备份策略，一方面维持数据的安全性，另一方面也可 保持sql server能顺畅运行。尽最人的努力减少由于数据的损坏对客户造成的 损失。概念1) 备份工作规划计算机软硬件的稳定性还未达到非常稳定的状态，计算机中的数据仍有损失或毁 坏的情况发生。存在于计算机内的数据不安全，即使采用容错设备，也无法保证 数据库100%安全。计算机硬件的费用可能很高，但计算机数据更珍贵，做好数 据备份，可保证在各种意外发生情况时，仍能保持有数据库的完整性。管理员 必须花时间来备份数

2、据库，但sql server也提供不少自动化功能。2) 备份方式数据库完全就是备份整个数据库的内容。若将整个数据库的内容都备份下来，需很多的存储 空间来存放备份数据，但其好处是在还原数据库时，也只耍将整个数据库从一份 数据库备份还原到sql server中就可以了。它是其他备份的基础，在做其他备 份之前，必须得做此种方式的备份。数据库差异(differential)只备份从上一次执行“完全”数据库备份后有改动的数据，因此所需要的备份时 间和备份空间都比“完全”方式少，所以此方式最适合作为经常性的备份方式。 事务日志(transaction log)只备份事务记录文件的内容，由于事务日志文件只会

3、记录我们在前一次数据库备 份或事务日志记录备份之后，对数据库所做的变动过程，也就是只记录某一段吋 间的数据库变动情况，因此在做事务记录备份z前，必须做一次“完全”的数据 库备份文件和文件组：如果数据库的内容分散存储于多个文件或文件组，而且数据库己非常大，大到进 行一次完整的数据库备份会有吋间和存储空间上的问题。这时就可以使用此方式 來备份数据库的部分文件或文件组。由于每次只备份部分的文件或文件组，因此 需做数次不同的备份才能完成整个数据库的备份。才用此方式备份也有个好处, 就是当毁坏的数据只是数据库中的某个文件或文件组时，也只要还原坏的文件或 文件组备份就可以了。3) 备份方案制订原则规划数据

4、库的备份策略，需要考虑备份的各项变量，必须同时考虑到还原作业的 进行。在做备份时，可考虑以下几点。(1) 有多少时间以及在那些时段可进行备份(2) 数据库变动的频率如何j/ j/ jz jz3 4 5 6 /( z/( z( z/k数据库大小当数据库出问题时，用户愿意等待多久让数据库还原完毕应采用何种备份方式，做怎样的搭配要使用哪种存储介质4）备份方案（1）完全数据库备份配合事务日志备份对于数据库数据比较大，但每天的变动的内容小，可考虑采用此方式。可定期做 一次完整的数据库备份，例如：每周或每月做一次“完全”数据库备份。然后再 以较段短的周期，如每天或每两天做一此事务口志备份。这样平时备份工作

5、负担 比较轻，发生以外时，可将数据述原到最近期的状态。（2）完全数据库备份配合差异式备份此方式是将前一种方式中的事务日志备份方式改成差异式备份。其优点是在做还 原吋，只需在做完最近一次数据库备份的还原后，再用前一天的差异备份来还原 就可以了，不像使用事务记录备份时，需一天一天还原。缺点是在比较后的口子 里，每次做差异备份所需的时间和空间会增加。（3）三种备份方式的综合使用由于事务记录备份和差异备份各有其优缺点，因此我们可以将两种方法结合起来 使用。对于数据信息非常重要，每1、2小吋备份一次，可能的损失只是1、2 个小时的数据。（4）当数据库过大，有不易备份的问题时，可以采用文件或文件组备份方式

6、， 可考虑将数据库分开存于不同的文件和文件组中，然后以文件和文件组的方式将 整个数据库分数次备份。此外，如果数据库中只有部分表经常变动，另一部分很 少变动，也可考虑将这两部分存于不同的文件和文件组中，然后用不同的备份频 率來备份其内容。采用此方式备份数据的缺点是：必须数次操作才能备份完数 据。（5）使用备用服务器采用此方式是再准备一台服务器，先在原服务器上做完全的备份，再把此备份放 到备份服务器上做还原，使两边的数据同步，以后可以定期对原数据库做事务日 志备份，把事务口志放到备份服务器上还原。当原服务器出现问题既可使用备份 服务器接上网络提供服务。注意：系统数据库的备份与还原是很多用户在定义数

7、据库备份方案中容易忽略的, 系统数据库中虽然不保存业务数据，但它保存了很多sql server的设置，如果 不备份系统数据库，那么在服务器发生故障导致系统损坏时，重新配置所有的 sql server设置将是一个噩梦demo 方案一数据库备份（常用方案）在sql server备份窗口，可以通过调度设置，将当前进行的备份处理设置为定 吋自动处理的作用。具体的步骤如下：1. 展开一个服务器组。2. 展开一个服务器（sql server实例）。3. 展开【数据库】，右单击要备份的数据库，在弹出的菜单中选择【所有任务】 【备份数据库】命令。4. 在随后打开的sql server备份窗口中，设置备份的相关

8、选项。参见前面的备 份方案单击【常规】选项卡，在【调度】项中，选中【调度】复选框，然后单击【调度】 复选框后的【】按钮。在随后出现的【编辑调度】对话框中，设置时间调度安 排。5. 设置完成后，单击【确定】按钮，sql server将执行备份操作，同吋在【sqlserver代理】一【作业】中自动建立对应的作业。6. 在【sql server代理】一【作业】窗口中，可以像处理普通作业一样修 改或删除数据库备份窗口中定义的备份计划。演示方案二数据转换法(dts)具体的方案就是：1 主服务器和备份服务器装载完全一样的操作系统和数据库软件，以及和应编制 的管理软件；2. 事先选定需要定期更新的表、视图、

9、存储过程等等，必要时可选择全部数据库 更新；3使用sql server提供的数据转换服务导入/导出向导(以sql server2000 为例)，利用复制 sql server 对象任务，将 microsoft sql server 对象从sql server的一个实例复制到另一个sql server中；4. 编辑反复岀现的作业调度，确定发生的频率和具体吋间，一般情况下，由 于数据量较大，不提倡每口发生多次，尽量安排在网络空余时间发生。 这个方案优点是：1成本低、可靠性高，操作简单，较易维护；2. 系统转换时间短，备份系统替换主服务器只需几分钟。即使出现意外，备 份系统很快就会恢复正常，只损失少

10、量数据，重新输入即可；3. 本地和异地备份都是利用晚上网络空闲时间进行的，没有增加口常网络流 量，不会增加网络负担。注意选择传输对象，相同版本的sql server可以相互，或者由低版本向高 版本sql server备份，如：sql server 7.0的实例备份到sqlserver 2000的实例。sql server 2000的实例备份到另一个sql server 2000的实例。备份的操作步骤：1. 打开企业管理器，选择sqlserver服务器的数据库，单击鼠标右键，选择“所 有任务”，并点选“导入数据”或“导出数据” o如果是在主服务器上进行操 作，选导岀数据；在备份服务器上进行操作，

11、则选导入数据。2第一步操作后，调出“数据转换服务导入/导出向导”界面，单击“下一步”， 进入“dts导入/导出向导”的选择数据源界面，选择“目的”，选择“制定表 复制或查询”，选择要复制的对象。弱做选择时，首先根据具体的客户端选择数据源，如果是ole db客户端，选择“用 于 sql server 的 microsoft 提供的 0ledb 程序”。然后选择提供数据源的服务器，接下來选择进入数据库的方式，可选择“使用 windows身份验证”或“使用sql server身份验证”。如果选“使用sql server身份验证”,须填写sql server认可并对数据有修改权的用户，一般 可选“sa

12、”及其密码。最后选择要复制的数据库。3. 在“制定表复制和查询”中，选择“在sql server数据库之间复制对象 和数据”，然后单击下一步。选择具体复制对彖和相应的选项等等。4. 通过保存调度和复制包，定制进行复制的时间和频率。5. 全部设置完成后，提示会列出源、目的、采用的方式、数据库等选项，由 你确认完成。6. 如果设置结束后要进行定制吋间修改，在企业管理器中，通过服务器、数 据库、管理、sql server代理、作业，然后选择具体的作业，调出界面进行修 改。至此，你就可以在自己的中小企业环境中，轻松实现多台数据库服务器间的定时 自动数据备份了。演示方案三数据库维护计划+xcopy命令（

13、推荐使用）1. 展开一个服务器组2. 展开一个服务器实例（sql server实例）。3. 展开【管理】，右击【数据库维护计划】，在弹岀的快捷菜单中选择【新建 维护计划】命令。4. 在随后出现的【数据库维护计划一（local）对话框中，单击【下一步】 按钮跳过欢迎页，在接下来的【选择数据库】步骤中，定义要备份的数据库。5. 单击【下一步】按钮，跳过与数据库备份无关的步骤，在【指定数据库备份 计划】步骤中，选择【作为维护计划的一部分来备份数据库】复选框；通过单击【磁带】或者【磁盘】单选按钮來选择备份设备；单击【调度】选项右边的【更 改】按钮，可以在随后出现的【编辑反复出现的作业调度】对话框中设置

14、维护计 划的时间安排。6. 单击【下一步】按钮，如果在步骤（5）中，备份设备选择的是【磁盘】设备， 则会进入【指定备份磁盘目录】步骤，根据实际情况，设置此步骤中的内容。7. 单击【下一步】按钮，进入【指定事务口志备份计划】步骤，如果需要进行 事务h志备份，则选择【作为维护计划的一部分来备份事务h志】复选框。备份 事务日志的其他设置步骤参考（5）（6） o8. 根据需要设置其他步骤，最后进入【正在完成数据库维护计划向导】步骤， 设置好【计划名】，单击【完成】按钮完成数据库维护计划的定义。9. 完成步骤（1）（8）后，单击【数据库维护计划】，在企业管理器控制台 右边的详细信息窗口中就可以看到定义好

15、的数据库维护计划，同时，展开【sql server代理】一【作业】，可以【作业】右边的详细窗口中，可以看到维护 计划自动建立的备份出路作业。10. 通过有机维护计划，选择【属性】命令，可以修改已经定义的维护计划。11通过右击维护计划，选择【删除】命令，可以删除已经定义的维护计划，同 时【sql server代理】一【作业】中的相关作业也会自动删除。新建一个批文件copy, bat具体脚本如下：echo offtitle正在传输数据库备份文件到远程备份服务器上！echo提示：开始验证服务器上是否存在备份文件！echo请稍候！if exist f:dbback*. bak （goto proces

16、s ） else goto unfound:processecho提示：在服务器上找到了备份文件！echo echo提示：开始删除远程备份服务器上的原始文件!rem备注：下列ip地址为远程备份服务器的ip地址!del 192. 168. 244. 24dbback /q /s /fdel 192. 168. 244. 24dbback< * /q /s /fi f error!evel 0 goto success:successecho提示：成功删除原始文件！echo echo提示：开始复制数据库备份文件到远程备份服务器w192. 168. 244. 24dbback!echo请稍候！

17、rem备注：请具体更改192. 168. 244. ldbback为服务器备份文件存放的位置。 w192. 16& 244. 24dbback为备份服务器上存放的位置！xcopy 192. 168. 244. ldbback 192. 16& 244. 24dbbeick /y /frem备注：当xcopy命令结束后返回的errorlevel为4说明磁盘控件不足。当 xcopy命令返回0时说明成功完成操作！if errorlevel 4 goto lowmemoryi f errorlevel 0 goto showmessage:lowmemoryecho警告：请查看磁盘空间

18、是否不足！:unfoundecho警告：在服务器上找不到备份文件，批任务将要结束。服务器备份工作似 乎没有正常进行，请确认服务器是否正常生成备份文件！goto exit:showmessageecho提不：批任务完成！echo提示：成功复制数据库备份到远程服务器w192. 16& 244. 24dbbeick! echo按任意键结束退出！echo:cxitpauseecho成功复制personnelm数据库备份到远程服务器192. 168. 244. 241上！pause利用windows任务计划來调度批任务1. 单击“开始”一“程序”一“附件”一“系统工具”一“任务计划”2. 双击“

19、新建任务计划”，弹岀“任务计划向导”单击“下一步”。在选择任 务窗体中选择“浏览”。我们选择要调度执行的copy, bat文件。3. 在接下来弹岀的窗体中为我们的任务计划起个名称并口选择执行频率。这里我们选择“每天”4. 在密码确认窗体中输入本机的密码。单击“下一步”选择“完成”。至此任 务计划设置完成。（有需要的话可以单击选择“在单击完成时，打开此任务的高 级属性”复选框以便对此任务进行高级设置！）方案四同步备份服务器（脚木）/*该部分参考了邹建的脚本*/*-说明：下面的代码演示了如何利用h志述原功能，将主数据库中的数据变化及时反馈到 备用数据库中备用数据库的数据可以随时用于查询，但不能被更

20、新（备用数据库只读）。* /首先，创建一个演示用的数据库（主数据库）create database db_teston（name 二 db_test_data,filename 二'c:db_test.mdf'）log on（name = db test log,filename =，c:db_test. ldf）go对数据库进行备份backup database db_tcst to disk= c:tcst_data. ba with formatgo把数据库还原成备用数据库（演示主数据库与这个备用数据库之间的同步）restore database db test bak

21、from disk=，c:test_data. bak，with replace,standby c:db_test_bak. idf,move ' db_test_data， to 'c:db_tcst_data. mdf',move ' db_test_log' to 'c:db_test_log.ldf'go启动sql agent服务exec master. . xp cmdshel1 5 net start sqlserveragent5,no_outputgo创建主服务器数据训与备用服务器数据库之间同步的作业declare jo

22、gid uniqueidentifierexec msdb. . sp add jobjob_id 二 jogid output,job_namc = n'数据同步处理'创眨同步处理步骤exec msdb. . sp add jobstepjob_id = jogid,step_name 二 n'数据同步©subsystem = 'tsql',©command = n，'主数据库中进行日志备份backup log db_test to disk二c:test_logbak with format备用数据库皿还原主数据库的口志备

23、份（应用主数据库屮的最新变化实际应该时主数据库备份与备用数据库的述原作业应该分别在主服务器和备 用服务器上建立，并且备份文件应该放在主服务器和备用都能访问的共享目录中 restore log db_test_bak from disk二'c:test_logbak' withstandby二'c:test_log. ldf',rctry_attempts = 5,retry_interval = 5创建論度（每分钟执行一次）exec msdb. . sp_add_jobschedulejob_id 二 jogid,namc = n'时间安排',f

24、req_type=4,freq_interval=l,freq_subday_type=0x4,freq_subday_i nterval = l,frcq_rccurrcncc_factor=l-添加目标服务愛exec msdb. dbo. sp add jobserverjob_id = jogid,server_name 二 n' （local）'go通过上述处理，主数据库与备用数据库之间的同步关系已经设置完成下面开始测试是否能实现同步在主数据库中创建一个测试用的表create table db_tcst. dbo. tb_tcst（id int）go等待1分钟30秒（由

25、于同步的时间间隔设置为1分钟，所以要延时才能看到效 果）wattfor delay '00:01:30'go查询一下备用数据库，看看同步是否成功select * from db test bak. dbo. tb test结果：_一td（所影响的行数为0行） -*/测试成功g0最后删除所有的测试drop database db test, db test bakexec msdb. sp_delete_job job_name=n，数据同步处理'go/*二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二

26、*/*服务器宕机处理说明使用这种方式建立的数据库同步，当主数据库不可用时(例如，主数据库损坏或 者停机检修)可以使用以下两种方法使备用数据库可用。-*/-1.如果主数据库损坏，无法备份岀最新的日志，可以直接使用下面的语句使 备用数据库可读写(丢失最近一次日志还原后的所有数据)。restore log db_test_bak with recovery-2.如果主数据库可以备份出最新口志，则可以使用下面的语句。先备份主数据库的最新的事务日志backup log db_test to disk二''c:test_logbak'' with format再在备用数据库

27、中恢复最新的事务日志，并且使备用数据库可读写(升级为主 数据库)restore log db_test_bak from disk二'c:test_log. bak'方案五复制订阅开始学习之前我们先搞清楚以下几个概念：复制(repliceition)就是将数据由某处复制到一个或多个目的地，由于对数据 库分散式应用的需求口渐增加，sql sever也提供了复制的功能。sql server的复制功能基本上是使用“发布服务器(publisher) ”和“订阅服 务器” (subscriber)的结构，这整个结构中包含了数个不同的组件。(1) 发布服务器(publisher):在复制中

28、，发布服务器就是负责发布数据库 内容给其他服务器的sql server (一般的我们把主服务器当作发布服务器，形 彖一点比喻发布服务器就好像是一本书的作者)(2) 订阅服务器(subscriber):订阅服务器就是指受发布服务器所发布数据 的sql server,通常发布服务器只有一个，但订阅服务器可以有多个。(形象 比喻订阅服务就像是读者，购买相同图书的读者可以有很多。)(3) 分发服务器(distributor):除了发布服务器和订阅服务器这两个基本 外，另一个可以由sql server在整个复制结构屮扮演的角色就是分发服务器。 分发服务器是负责将发布服务器所发布的数据库发布、传输到各订阅

29、服务器的服 务器。在简单的复制结构中，发布服务器和分发服务器通常是使用同一个sql servero设置为分发服务器的sql server,会有个名为distribution的系统 数据库存放复制信息。(4) 发布项(article):发布服务器所发布的数据称为发布项，发布项是进 行复制时的复制单位，发布项可以是表或存储过程等数据库对象，也可以知识表 中的某儿列或某儿项数据。(5) 发布(publish): 一组特定的发布项组合就称为发布，也就是sql server 进行复制吋所复制的数据合。当订阅服务器向发布服务器订阅数据时，是以发布 为单位，而不是以发布项为单位的。即，订阅服务器只能接收整个

30、发布中的所有 articleo而不能只选择其中儿个发布项类订阅，每个发布只有来自单一数据库 的内容，但我们可以为数据库设置多个发布。(6) 为适应不同的复制需求，sql server提供了发送(push)和请求(pull) 两种不同的订阅方式。区别如下：(a) 发送订阅(push subscription也称为强制订阅):所谓的发送订阅就是 由发布服务器主动传送发布给订阅服务器，也就是由发布服务器控制复制的操作, 只要指定进行复制的时间一到，不管订阅服务器有没有提击要求，发布服务器都 会将数据传送给订阅服务器。(b) 请求订阅(pull subscription):请求订阅和发送订阅相反，是由

31、订阅 服务器自行根据排定的h程向发布服务器要求订阅发布，通常在订阅服务器数量 较多，或是不需要即时取的最新的数据内容时，可采用此种订阅方式。步骤：下介绍实现复制的步骤。(以快照复制为例)准备工作：1. 发布服务器，订阅服务器都创建一个同名的windows用户，并设置相同的密码, 做为发布快照文件夹的有效访问用户-我的电脑控制面板-管理工具计算机管理-用户和组-右键用户新建用户 _-建立一个隶属于administrator组的登陆windows的用户2. 在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录，操作： 我的电脑一d:新建一个目录，名为：bakpub右键这个新建的目录屈性一

32、共享选择共享该文件夹通过权限按纽来设置具体的用户权限，保证第一步中创建的用户具有对该文 件夹的所有权限-确定3. 设置sql代理(sqlserveragent)服务的启动用户(发布/订阅服务器均做此设 置)开始-程序-管理工具-服务-右键 sqlserveragent属性一登陆一选择此账户-输入或者选择第一步中创建的windows登录用户名 密码中输入该用户的密码4. 设置sql server身份验证模式，解决连接时的权限问题(发布/订阅服务器均做此设置）企业管理器右键sql实例一属性安全性一身份验证-选择sql server 和 windows"-确定5. 在发布服务器和订阅服务器上互相注册企业管理器-右键 sql server 组-新建sql server注册下一步一可用的服务器中，输入你要注册的远程服务器名一添加-下一步-连接使用，选择第二个sql server身份验证"下一步一输入用户名和密码-下一步-选择sql server组，也可以创建一个新组-下一步-完成6. 对于只能用1p,不能用计算机名的，为其注册服务器别名（在连接端配置，比如，在订阅服务器上配置的话，服务器名称中输入的是发布服 务器的ip）开始-程序-microsoft sql server-客户端网络实用工具-别名