笔者根据近一段时间所学的数据库知识编写了这篇关于SQL Server如何在项目中实现备份与还原的文章,与大家相互探讨、学习。
--备份的设备有2种(临时设备和***设备) 注意:默认下的备份类型是完整备份
--***种:
- backup database Company to disk='d:\backup\1.bak'
--临时设备
/*如果这里不指定明确路径的话(如:backup database company to disk='backup\1.bak'),那么备份的数据库将会自动备份到系统指定的目录下:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup*/
--第二种:
/****步首先建立***备份设备 (系统自带的存储过程)在master 数据库中就会找到如图1:
*/
--执行语句如:
- exec sp_addumpdevice 'disk','disk_company','D:\2.bak'
--***设备
--执行结果就会出现如图2:
--多了一个备份设备:disk_company
--第二步:
- backup database company to disk_company with noinit
--默认表示追加(不覆盖)
--好了 备份完成 !
--现在我来还原数据库(我用的是***种方法备份的,所以我要***种方法来还原) 。
--原来的数据如图3:
--经过我手动删除几个表后的数据库如图4:
--执行语句:
- restore database Company from disk='d:\backup\1.bak'
--注意备份到哪里去就要从还原哪里来
--执行后会出现什么呢?请看错误消息:
- /*消息 3159,级别 16,状态 1,第 1 行
- 尚未备份数据库 "company" 的日志尾部。如果该日志包含您不希望丢失的工作,请使用 BACKUP LOG WITH NORECOVERY 备份该日志。
- 请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。
- 消息 3013,级别 16,状态 1,第 1 行
- RESTORE DATABASE 正在异常终止。*/
--为什么会出现这种错误呢 我们可以从错误的消息中找到解决方案!
--我们去看看这个数据库的恢复模式如图5:
--因为如图的恢复模式是 :完整; 所以它的功能是将所有事务都写入日志,把所有数据库文件的都还原
--方案一:我现在只是还原的数据库文件 并没有备份日志文件 所以我再去备份日志文件
- backup log Company to disk='d:\backup\2.bak'
--备份日志文件
- restore database Company from disk='d:\backup\1.bak'
--再去还原数据库
- restore log Company from disk='d:\backup\2.bak'
--这步可有可无
--执行的结果为:如图6:
--方案二 由于错误消息中的提示:请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。
---消息 3013,级别 16,状态 1,第 1 行 所以 我想到去覆盖掉日志文件 虽然恢复模式是完整的 但是我要覆盖它 也是可以的
--只是对数据库的操作没有日志没有完全还原而已 也是可以的
--执行语句如下:
- restore database Company from disk='d:\backup\1.bak' WITH REPLACE
--执行成功
- /*已为数据库 'Company',文件 'Company_Data' (位于文件 1 上)处理了 224 页。
- 已为数据库 'Company',文件 'Company_Log' (位于文件 1 上)处理了 5 页。
- RESTORE DATABASE 成功处理了 229 页,花费 0.225 秒(8.319 MB/秒)。*/
--方案三:我想了一下 我只是备份了数据库,但是没有备份日志文件 根据备份还原的原理
- /*
- 恢复模式 说明
- 简单 不用备份的事务日志,即可还原
- 用于小型数据库和不经常更改的数据库
- 完整 所有事务都被记录到日志中
- 保留所有日志,直到事务日志备份
- 用于生产数据库
- 大容量日志 完整恢复模式的补充
- 不将大容量日志操作写入日志
- */
--所以我修改了这个数据库的属性中的恢复模式 改为 “简单”
--如图7:
--我直接执行还原的代码
- restore database Company from disk='d:\backup\1.bak'
- /*执行结果:
- 已为数据库 'Company',文件 'Company_Data' (位于文件 1 上)处理了 224 页。
- 已为数据库 'Company',文件 'Company_Log' (位于文件 1 上)处理了 5 页。
- RESTORE DATABASE 成功处理了 229 页,花费 0.224 秒(8.356 MB/秒)。*/
--三种还原的解决方案成功
--但是这用到项目中数据库正在使用的话是不成功的 ,它具有排它性 !
--所以我写了一个存储过程来解决,这也是很多程序员花了很久才解决的问题
--代码用法如下 :有附带的例子下载
--创建存储过程 killspid
- create proc killspid (@dbname varchar(20))
- as
- begin
- declare @sql nvarchar(500)
- declare @spid int
- set @sql='declare getspid cursor for
- select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
- exec (@sql)
- open getspid
- fetch next from getspid into @spid
- while @@fetch_status < >-1
- begin
- exec('kill '+@spid)
- fetch next from getspid into @spid
- end
- close getspid
- deallocate getspid
- end
- GO
--说明:
--1.此存储过程应写在Master中;
--2.以上代码就是解决因为数据库正在使用,所以未能获得对数据库的排它访问权的问题,不然系统有时会报错;
我附带一个简单的备份还原的例子 (ASP.NET +SQL SERVER 2005 的运行环境)
http://files.cnblogs.com/qinpengming/BackUpDBSolution.rar
有时间我会写一篇关于怎样还原到指定时间点的例子 我会采用完整备份 差异备份 日志备份 我也会把原数据库文件也损坏掉 再去还原的! !!!!!!!!!!
原文出处:http://www.cnblogs.com/qinpengming/archive/2011/03/07/struggle.html
【编辑推荐】
- 浅析SQL Server 2008中的代码安全之一:存储过程加密与安全上下文
- 浅析SQL Server 2008中的代码安全之二:DDL触发器与登录触发器
- 浅析SQL Server 2008中的代码安全之三:通过PassPhrase加密
- SQL Server安全解析
- sql server安全的两层模型