SQLSERVER备份和还原

Bertha 。 2022-07-10 04:07 917阅读 0赞

### SQLSERVER备份和对日志的处理 ###

一般的情况下我们会选用SQL Server自带的数据库维护计划来定义备份，但有些时候,有的机器数据库维护计划不能成功实现备份，错误原因可能不尽相同, 这时我们可以用简单的SQL Server备份脚本来做备份。生成简单的SQL Server备份脚本的SQL语句:  
**use master  
select 'backup database '+name+' to disk=''F:/sqlserver\_backup/'+name+'.bak'' with init' from sysdatabases**  
===>>得到如下备份脚本：  
**backup database master to disk='F:/sqlserver\_backup/master.bak' with init  
backup database model to disk='F:/sqlserver\_backup/model.bak' with init  
backup database msdb to disk='F:/sqlserver\_backup/msdb.bak' with init  
backup database db\_app1 to disk='F:/sqlserver\_backup/db\_app1.bak' with init  
backup database db\_app2 to disk=F:/sqlserver\_backup/db\_app2.bak' with init**  
**另：在SQL Server的企业管理器中，点击“帮助-->帮助主题”然后点开“SQL Server联机丛书”可以获得此命令的详细说明。**

**第一部分：问题的来源**  
        数据库的日志文件是记录数据库 **UPDATE,DELETE** 操作的踪迹,也是当数据库发生意外(如黑客攻击,系统数据库发生破坏破坏等)时我们能够让数据库恢复至特定时刻的保证, 所以它很重要!我们应该对数据库进行日志备份!  
        我们现在来分析一下数据库故障的种类：

一、事务内部的故障  
        也就是我们不能够保证事务的一致性状态,举个例子来说,您有两个银行帐户,由于某种原因,您现在需要从一个银行帐户上取出100万存到另一个银行帐户上, 但是由于银行数据库的故障,您的一个银行帐户确实少了100万,但是另一个帐户没有相应的增加100万,您现在会怎么做?当然我们可以在相应事务中加入**ROLLBACK**来强抽回滚。  
   
二、系统故障(软件故障-**SOFT CRASH**)  
        系统故障是指造成系统停止运转的任何事件,舍不得系统要重新启动.如特定类型的硬件故障(CPU故障),操作系统故障,DBMS代码故障,突然停电等,这类故障影响正在运行的事务,但是不破坏数据库.这时所有内存的数据全部丢失,所有事务非正常终止,一些没有完成的事务的结果可能已经送给了物理数据库,从而数据库中的数据处于不正确状态,为保证数据的一致性,需要清除这些事务对数据库的篡改! 这时系统重新启动后,对所有已经提交的事务REDO,对所有非正常终于的事务UNDO来强行撤消所有未完成的事务!  
   
三、介质故障(硬件故障--HARD CRASH)  
        这是一种外存故障,如磁盘损坏,磁头碰撞,瞬时强磁场干扰等.这类故障可能性最小,但破坏性最大。  
   
四、计算机病毒  
  略……  
   
**第二部分： "冗余"技术**        
        上述故障数据库进行恢复的原理十分简单，就是两个字"冗余"现在我们就说一下如何建立"冗余"技术，本章主要包括下面几个方面的内容:  
  1、在SQL SERVER 中我们可以设置数据库的恢复模型为完全恢复模型,大容量插入模型和简单模型  
  2、什么时候需要我们进行数据库的备份  
  3、有几种数据库备份方法  
  4、演示几种备份方法(用备份前的准备工作)  
  5、几种备份策略   
**   注：上面的的内容可以在BOOK ON LINE(SQL Server联机丛书)里找到非常好的答复**  
        
**一、问题演习：**  
**0、首先要有一个完全备份(不然的话您是不能够成功的备份日志文件)**  
**backup database Northwind  to nwbakup1,nwbakup2  with init**  
查看备份信息  
**RESTORE FILELISTONLY FROM nwbakup1,nwbakup2**  
也可以在企业管理器中SQL服务器-->Northwind-->右键-所有任务-->备份数据库，然后在图形界面下进行相应的操作，这是非常简单的

**1、use northwind  
insert employees(lastname,firstname) values('aaa','aaa')  
delete from employees where lastname='aaa'**  
记下这时的时间(比如现在时间是**23:35**)

**2、过3分钟输入**  
**use northwind  
insert employees(lastname,firstname) values('bbb','bbb')**

**3、此时select \* from employees 将能看到这两条记录**

**4、进行日志备份**

选中northwind数据库-->所有任务-->backup database-->在对话框中选择事务日志备份为备份类型，选择已有的备份设备，和追加到媒体，单击ok进行备份。这样生成了两个新纪录的日志备份。  
**backup log Northwind to nwlogbakup**

**5、以下演示恢复**  
northwind数据库-->所有任务-->restore database -->对话框中选择时点还原-->输入刚才记录的时间-->单击ok恢复  
**restore database Northwind from nwbakup1,nwbakup2 with norecovery**  
如果出现以下提示信息：  
use Northwind  
服务器: 消息 927，级别 14，状态 2，行 1  
无法打开数据库 'Northwind'。该数据库正处于还原操作中途。  
这是因为我们指定了选项" with norecovery"  
NORECOVERY指示还原操作不回滚任何未提交的事务。如果需要应用另一个事务日志，则必须指定 NORECOVERY 或 STANDBY 选项。如果 NORECOVERY、RECOVERY 和 STANDBY 均未指定，则默认为 RECOVERY。当还原数据库备份和多个事务日志时，或在需要多个 RESTORE 语句时（例如在完整数据库备份后进行差异数据库备份），SQL Server 要求在除最后的 RESTORE 语句外的所有其它语句上使用 WITH NORECOVERY 选项。

**6、现在恢复事务日志至23:35**  
**restore log Northwind from nwlogbakup with recovery, stopat='2008-06-16 23:35'**  
注：STOPAT = date\_time | @date\_time\_var指定将数据库还原到其在指定的日期和时间时的状态。如果对 STOPAT 使用变量，则该变量必须是 varchar、char、smalldatetime 或 datetime 数据类型。只有在指定的日期和时间前写入的事务日志记录才能应用于数据库。  
下例将数据库还原到其在 1998 年 4 月 15 日中午 12 点时的状态，并显示涉及多个日志和多个备份设备的还原操作。  
RESTORE DATABASE MyNwind  
   FROM MyNwind\_1, MyNwind\_2  
   WITH NORECOVERY  
RESTORE LOG MyNwind  
   FROM MyNwindLog1  
   WITH NORECOVERY  
RESTORE LOG MyNwind  
   FROM MyNwindLog2  
   WITH RECOVERY, **STOPAT = 'Apr 15, 1998 12:00 AM'**

**7、执行select \* from employees ,此时只能看到第一个新插入的记录**   
查看备份信息：  
**RESTORE FILELISTONLY FROM DISK ='C:/Program Files/Microsoft SQL Server/MSSQL/BACKUP/db1\_backup'  
DBCC SQLPERF (LOGSPACE)** \--查看日志空间

**二、日志备份：**  
**1、备份日志  
BACKUP LOG 数据库名 TO DISK='c:/aalogbak' WITH INIT,NO\_TRUNCATE**  
这样你的日志清空，也备份了，你可以压缩在维护日志备份序列时，调度 BACKUP LOG 语句按间隔发生，以使事务日志不致增长到超过预期的大小。  
**如果不想要日志或者是日志已没有什么作用时,可以考虑以下的实现方案:**  
**backup log DBNAME with \[no\_log|truncate\_only\]\[no\_truncate\]**  
/\*  
NO\_LOG | TRUNCATE\_ONLY 无须备份复制日志即删除不活动的日志部分，并且截断日志。该选项会释放空间。因为并不保存日志备份，所以没有必要指定备份设备。NO\_LOG 和 TRUNCATE\_ONLY 是同义的。使用 NO\_LOG 或 TRUNCATE\_ONLY 备份日志后，记录在日志中的更改不可恢复。为了恢复，请立即执行 BACKUP DATABASE。  
NO\_TRUNCATE 允许在数据库损坏时备份日志。  
\*/

**2、收缩数据库文件  
select fileid,filename from sysfiles  
USE DBNAME   
DBCC SHRINKFILE (2,10) -- 把fileid=2 的文件收缩到 10 MB**  
或者：  
企业管理器-->右键你要压缩的数据库-->所有任务-->收缩数据库-->收缩文件-->选择日志文件-->在收缩方式里选择收缩至XXM-->这里会给出一个允许收缩到的最小M数-->直接输入这个数-->确定就可以了

**3、 删除LOG**  
分离数据库 -->删除LOG文件 -->附加数据库  
此法生成新的LOG，大小只有500多K

**4、你可以先配置你的RECOVERY MODEL 为SIMPLE  
ALTER DATABASE DB\_NAME SET RECOVERY SIMPLE**

**5、exec sp\_dboption 'test2','autoshrink','on'**  
当autoshrink为 true 时，数据库文件将成为自动周期性收缩的候选文件

**第三部分：备份或恢复的其他选项**

**1、 备份文件循环使用(即省去了删除过期备份文件的操作): with format**

backup database 数据库名 to disk='c:/a\_db.bak'  with format;

backup log          数据库名 to disk='c:/a\_log.bak' with format;

**2、 恢复数据库时出现异常或断电，重新开始备份: with restart**

RESTORE database 数据库名 FROM disk='d:/test2\_db.bak'  WITH restart;

**3、 备份历史记录表**

SQL Server 包括以下跟踪备份活动的备份历史记录表：  
     backupfile (Transact-SQL)  
     backupfilegroup (Transact-SQL)  
     backupmediafamily (Transact-SQL)  
     backupmediaset (Transact-SQL)  
     backupset (Transact-SQL)

**4、 执行数据库完整还原（简单恢复模式）**

[http://msdn.microsoft.com/zh-cn/library/ms186216.aspx][http_msdn.microsoft.com_zh-cn_library_ms186216.aspx]

**5、 执行数据库完整还原（完整恢复模式）**

必须先备份结尾日志，以确保日志不再更改：BACKUP LOG 数据库名称 TO <备份设备> WITH NORECOVERY

[http://msdn.microsoft.com/zh-cn/library/ms187495.aspx][http_msdn.microsoft.com_zh-cn_library_ms187495.aspx]

[http://msdn.microsoft.com/zh-cn/library/ms189627.aspx][http_msdn.microsoft.com_zh-cn_library_ms189627.aspx]

[http_msdn.microsoft.com_zh-cn_library_ms186216.aspx]: http://msdn.microsoft.com/zh-cn/library/ms186216.aspx
[http_msdn.microsoft.com_zh-cn_library_ms187495.aspx]: http://msdn.microsoft.com/zh-cn/library/ms187495.aspx
[http_msdn.microsoft.com_zh-cn_library_ms189627.aspx]: http://msdn.microsoft.com/zh-cn/library/ms189627.aspx