SQLSERVER备份和还原

最新推荐文章于 2022-03-26 21:38:30 发布

xiaoxu0123

最新推荐文章于 2022-03-26 21:38:30 发布

阅读量1.8k

点赞数

分类专栏： SQL Server 文章标签： sqlserver 数据库 sql server database disk insert

SQL Server 专栏收录该内容

110 篇文章 1 订阅

订阅专栏

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

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

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

http://msdn.microsoft.com/zh-cn/library/ms187495.aspx

http://msdn.microsoft.com/zh-cn/library/ms189627.aspx