SQL SERVER 2005 数据库备份 ---完整备份+日志备份

数据库备份的重要性，是没得说了。

数据库备份实验：
数据库名称：Demo
恢复模式：完整
数据库版本：SQL SERVER 2005+SP2 OR SP3

备份策略：完整备份+日志备份

开始吧。。。。。

准备工作。。。。。。为了说明数据库备份我们新建数据库 Demo

create database Demo on primary(name='Demo',filename='D:/Data/Demo.mdf',size=100,filegrowth=204800kb)log on(name='Demo_log',filename='D:/Log/Demo.ldf',size=50,filegrowth=10240kb)go---创建测试表use DemogoCREATE TABLE T1(ID INT NOT NULL IDENTITY(1,1),SNAME VARCHAR(20) NULL,CITY VARCHAR(20) NULL)GO---插入测试数据use DemogoINSERT INTO T1 SELECT 'JACK1','北京1' UNION ALL SELECT 'JACK2','北京2' UNION ALL SELECT 'JACK3','北京3' UNION ALL SELECT 'JACK4','北京4'GOuse DemogoSELECT * FROM T1GO

1、完整备份数据库

-----备份数据库脚本backup database [Demo] to disk='e:/Demo_full.bak' WITH CHECKSUM,NOFORMAT,INIT,SKIP---或者---我们在实际环境中，为了自动获取数据库的 备份日期 我们执行下面的脚本备份数据库declare @d varchar(8)declare @dbname varchar(100)declare @sql varchar(max)set @d=convert(varchar(8),getdate(),112)set @dbname='Demo' --数据库名称set @sql='backup database ['+@dbname+'] to disk=''e:/'+@dbname+'_full_'+@d+'.bak'' WITH CHECKSUM,NOFORMAT,INIT,SKIP'--print @sqlexec (@sql)

说明：我们在备份数据库的时候最好加上 WITH CHECKSUM 参数,对数据库的页进行验证，错误检查。免得我们备份的是一个有问题的数据库。
       SQL SERVER 2000 里验证请用 DBCC CHECKDB 检查数据库是否完整。

2、完整备份数据后第一次备份日志

----备份日志脚本backup log [Demo] to disk='e:/Demo_log_1.bak'---或者---我们在实际环境中，为了自动获取数据库的 备份日期和时间 我们执行下面的脚本备份日志declare @d varchar(16)declare @dbname varchar(100)declare @sql varchar(max)set @d=replace(replace(replace(convert(varchar(16),getdate(),120),'-',''),' ',''),':','') --年月日时分set @dbname='Demo' --数据库名称set @sql='backup log ['+@dbname+'] to disk=''e:/'+@dbname+'_log_'+@d+'.bak'''--print @sqlexec (@sql)

3、我们再插入几条，产生新的日志和数据

INSERT INTO T1 SELECT 'MAYY1','上海1' UNION ALL SELECT 'MAYY2','上海2' UNION ALL SELECT 'MAYY3','上海3' UNION ALL SELECT 'MAYY4','上海4'

4、完整备份数据后 第二次备份日志。

----备份日志脚本backup log [Demo] to disk='e:/Demo_log_2.bak'---或者---我们在实际环境中，为了自动获取数据库的 备份日期和时间 我们执行下面的脚本备份日志declare @d varchar(16)declare @dbname varchar(100)declare @sql varchar(max)set @d=replace(replace(replace(convert(varchar(16),getdate(),120),'-',''),' ',''),':','') --年月日时分set @dbname='Demo' --数据库名称set @sql='backup log ['+@dbname+'] to disk=''e:/'+@dbname+'_log_'+@d+'.bak'''--print @sqlexec (@sql)

数据库完整备份和日志备份模拟结束。

--------数据库还原--------

数据库备份完成了。我们来做 Demo 数据库还原
-------------------------------------------
1、数据库还原脚本.因为有后续的日志需要还原，我们选择数据库还原状态为 with norecovery

restore database Demo from disk='e:/Demo_full.bak' with norecoverygo-----或者---- 如果需要移动原来数据库的MDF 和LDF 文件 到别的目录，需要使用 with move 关键字restore database Demo from disk='E:/Demo_full.bak' with move 'Demo' to 'D:/Data/Demo.mdf',move 'Demo_log' to 'd:/Log/Demo_log.ldf',NORECOVERYgo

2、第一次还原数据库日志脚本。我们选择日志还原状态为 with norecovery

restore log Demo from disk='e:/Demo_log_1.bak' with norecoverygo

3、第二次还原数据库日志脚本。我们选择日志还原状态为 with recoveryrestore log Demo from disk='e:/Demo_log_2.bak' with recoverygo

 

4、我们来看看还原后的数据库数据量是不是和原来的数据量一致。

use demogoselect * from t1go

数据库还原完成。。。

注意：由于各种原因，可能我们的数据库不是一个人管理，可能会有的同事，由于工作需要，手动备份日志或者数据库，这样的备份对我们以后的数据库
   还原是否有影响呢？

假设以Demo 为例说明：

我们设置成作业备份数据库和日志
数据库完整备份： 每天完整备份一次。
日志备份：每天备份2次
提醒：备份日志的时间一定要在完整备份数据库之后，要不然 日志备份会失败。。。

假设我们对数据库的操作是这样的。。。
A、作业自动执行，完整备份完成。
B、作业自动执行，第一次备份日志完成
C、在某个时间点由于工作需要，临时手动执行备份日志，备份的这个日志文件，很有可能丢失，没有保存。
 backup log [Demo] to disk='e:/Demo_log.bak'
D、作业自动执行，第二次备份日志完成。

这种情况，我们利用A,B,D 三次备份的文件，还原数据库时，会失败。
因为C 这一步备份日志，已经打乱了日志的LSN号.(假如C这一步的备份日志已经找不到了)

--在数据库备份后，如果执行下列语句，后续的事务日志备份也会失败。
use Demo
go
backup log Demo with truncate_only
go
dbcc shrinkfile (demo_log)
go

那么如果在第一次和第二次备份日志中间，临时手动完整备份数据库，
我们这个手动备份的数据库也没有保留，在利用第一次备份的数据库，和后面两次备份的日志能不能成功还原数据库呢？
答案是：能成功还原。

如果做差异备份数据库，某个时间点临时备份数据库，而且没有保留，在做还原的时候会失败。

 

5、 删除过期的数据库和日志备份文件。

   我们设置成作业定期备份数据库和日志，这样备份文件会越来越大，硬盘空间会变得越来越小，需要我们把旧的数据备份文件删除掉。

删除5天前的备份文件。适用数据库备份和日志备份

 use mastergodeclare @d datetimeset @d=getdate()-5execute master.dbo.xp_delete_file 0,N'E:/',N'bak',@d,1go