SQL SERVER学习（三）——SQL Server 数据库的自动备份和删除，以及bak文件的还原

admin管理员组

文章数量:1794759

SQL SERVER学习（三）——SQL Server 数据库的自动备份和删除，以及bak文件的还原

一、基本备份方法：

（1）如图：选中需要备份的数据库，右击数据库 选择 ：“任务”—“备份”： 点击备份出现：

备份的类型：

（1）完全备份：它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但是呢，它也需要花费更多的时间和空间。

（2）事物日志备份：事务日志备份的内容是从还未被备份的事务日志开始，直到备份结尾的最后一个事务日志为止。

（3）差异性数据库备份：包括自从上一次完整性备份以来所有改变的数据页，以及备份结尾的部分活动事物日志。

当然具体该选取那种备份方式，参考： blog.csdn/tjvictor/article/details/5214035

点击添加，选择备份路径，点击确定，备份成功！ 在备份路径，发现出现一个后缀名为.bak数据库文件。

二、使用SQL SERVER维护计划进行自动备份

自动备份需要的数据库，在时间、效率上都要比手动备份更加优化。

首先保证Sql Server的代理服务是开启的。右键点击SQL Server代理–启动 选中“对象资源管理器”中的“管理”下的“维护计划”，右击选择“维护计划向导”

单击“下一步”

在如下弹出的“维护计划向导”界面中，输入名称，选择“整个计划统筹安排或无计划”，单击“更改”。 设置自动备份的频率，单击“确定”按钮。 单击下一步后，界面如下图；勾选备份数据库，此时可以选择：完整、差异、事务日志三种类型的备份 单击下一步，选择需要备份的数据库。比如勾选了“beifen”。

下一步，选择备份目标地址。 最后选择完成，最终结果：

三、使用SQL Server代理进行自动备份

右击SQL Server代理 作业，新增作业 选择步骤，点击新建

输入步骤名称（随意取），选择数据库 在下方命令窗口中输入：

DECLARE @strPath NVARCHAR(200) set @strPath = convert(NVARCHAR(19),getdate(),120) set @strPath = REPLACE(@strPath, ':' , '-') set @strPath = 'E:\\backup_ceimm2\\' +'ceimm2_DB_' +@strPath + '.bak' BACKUP DATABASE ceimm2 TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

说明：备份选中的数据库（如ceimm2 ）到E:\\backup_ceimm2 路径下， 并且备份文件取名格式 如：ceimm2_DB_2019-01-06 01-00-00 2019-01-06 01-00-00 为任务计划执行时间。

选择计划，新建计划 设置在每天1：00备份，所以前面文件生成格式的由来。

四、使用SQL Server代理进行备份数据库文件的自动删除

前面备份了数据库文件，如果手动删除是不现实的

步骤类似目录三，新建作业， 输入命令：

DECLARE @OLDDATE DATETIME SELECT @OLDDATE=GETDATE()-20 EXECUTE master.dbo.xp_delete_file 0,N'E:\\backup_ceimm2\\',N'bak',@olddate

说明：删除E:\\backup_ceimm2 目录下，格式为bak的20天前的文件。

五、使用SQL Server维护计划进行备份数据库文件的自动删除

进入管理-维护计划

步骤类似目录二，新建维护计划，

六、bak数据库文件的还原

打开SQL server 数据库，右击数据库在列表中选择还原数据库，会弹出还原数据库的窗口，选择原设备按钮，然后点击原设备右边的文件选择按钮。

选择指定目录下bak文件

目标数据库需要与你要还原的数据库一致，在下拉菜单中有，直接选择就可以了。 如果出现

标题: Microsoft SQL Server Management Studio ------------------------------ 还原数据库“frame”时失败。 (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ 其他信: System.Data.SqlClient.SqlError: 因为数据库正在使用，所以无法获得对数据库的独占访问权。 (Microsoft.SqlServer.SmoExtended) 有关帮助信，请单击: go.microsoft/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=1100.60+((SQL11_RTM).120210-17+)&LinkId=206 ------------------------------ 按钮: 确定 ------------------------------

数据库还原问题：

System.Data.SqlClient.SqlError: 因为数据库正在使用，所以无法获得对数据库的独占访问权。 (Microsoft.SqlServer.SmoExtended)。出现此问题的原因是在还原数据库时，有其他用户正在使用数据库。还原数据库要求数据库工作在单用户模式。通常就是DBA在操作时，不允许其他用户连接数据库。

解决方案1.

如果你使用管理工具还原数据库并且在Microsoft SQL Server Management Studio 2016或以上版本的话，可以在还原的时候勾选"关闭到目标数据库的现有链接"

解决方案2：

如果你的SqlServer Management Studio的版本比较低，可以设置数据库为单用户模式，执行完还原操作后，恢复为多用户模式

方法一（最方便）：右键点击数据库 -> 属性 -> 选项 -> 状态 -> 限制访问 -> 选择Single-> 确定。然后还原。

方法二（最直接）：断开数据库连接

方法三（最暴力）：注销/重启数据库服务器

方法四（最麻烦）：写代码修改数据库相关属性，虽然麻烦，有的时候还是要用到，那就用到的时候再研究。

方法五（终级解决方案）： 关键SQL语句：

ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE 用完之后再 ALTER database [ datebase] set online

---

如果你觉得本篇文章对你有所帮助的话，麻烦请点击头像右边的关注按钮，谢谢！

技术在交流中进步，知识在分享中传播

本文标签： 自动备份数据库文件SERVERSQL