使用存储过程实现数据库备份

最新推荐文章于 2024-09-14 19:55:29 发布

nzk_bao713

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量487

点赞数

分类专栏： [02]SQL数据库文章标签：数据库存储 sql server xp windows database

[02]SQL数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

CREATE proc [dbo].[bakup_DataBase] as Declare @strPsw varchar(50) Declare @strUsr varchar(50) Declare @strCmdShell varchar(300) Declare @strDataBaseName varchar(20) Declare @FullFileName Varchar(200) Declare @FileFlag varchar(50) Declare @ToFileName varchar(200) Declare @SQLStr varchar(500) Declare @SQLStr2 varchar(500) Declare @FlagDel varchar(20) Set @FileFlag=replace(replace(convert(char(20),getdate(),20),':','-'),' ','-') --备份的文件命名规则:日期-时间.bak Set @strUsr='192.168.1.10/administrator' --需填写域名/用户名（目标机器的Windows登陆名) Set @strPsw='123456'--需填写Windows登陆密码(如:Soondy) Set @strCmdShell= 'net use //192.168.1.10/ ' + @strPsw + ' /user:' +@strUsr --需填写IP(目标机器的IP地址，如:192.168.2.178) Set @strDataBaseName='Example'--填写数据库名称(如:Soondy) Set @FullFileName='//E:/'+@FileFlag+'.BAK'--需填写本地备份临时文件的目录，因为需要先备份到本地再copy到目标机器(如:保存目录为E:/SoondyTest/) Set @ToFileName='//192.168.1.20/SharedDocs/' --需填写IP(目标机器的IP地址)以及保存的目录(如://192.168.2.178/c$/Test/) Set @FlagDel='false'--填写True表示删除本地的备份临时文件，填写False或其他字符表示保留该文件 Set @SQLStr='copy '+@FullFileName+' '+@ToFileName Set @SQLStr2='del ' +@FullFileName BackUp DataBase @strDataBaseName To Disk= @FullFileName with init exec master..xp_cmdshell @strCmdShell--尝试连接到目标机器 exec Master..xp_cmdshell @SQLStr --拷贝到目标机器上 if (@FlagDel ='True') exec master.. xp_cmdshell @SQLStr2--删除本地的备份临时文件

SQL Server 2005中并没有开启“xp_cmdshell”服务。开启方法如下：

xp_cmdshell 扩展存储过程将命令字符串作为操作系统命令 shell 执行，并以文本行的形式返回所有输出。由于xp_cmdshell 可以执行任何操作系统命令，所以一旦SQL Server管理员帐号（如sa）被攻破，那么攻击者就可以利用xp_cmdshell 在SQL Server中执行操作系统命令，如：创建系统管理员，也就意味着系统的最高权限已在别人的掌控之中。由于存在安全隐患，所以在SQL Server 2005中， xp_cmdshell 默认是关闭的。
　　两种方式启用xp_cmdshell
　　1.打开外围应用配置器—>
　　功能的外围应用配置器—>
　　实例名Database Enginexp_cmdshell—>
　　启用
　　2.sp_configure
　　-- 允许配置高级选项
　　EXEC sp_configure 'show advanced options', 1
　　GO
　　-- 重新配置
　　RECONFIGURE
　　GO
　　-- 启用xp_cmdshell
　　EXEC sp_configure 'xp_cmdshell', 0
　　GO
　　--重新配置
　　RECONFIGURE
　　GO
　　--执行想要的xp_cmdshell语句
　　Exec xp_cmdshell 'query user'
　　GO
　　--用完后,要记得将xp_cmdshell禁用(从安全角度安全考虑)
　　-- 允许配置高级选项
　　EXEC sp_configure 'show advanced options', 1
　　GO
　　-- 重新配置
　　RECONFIGURE
　　GO
　　-- 禁用xp_cmdshell
　　EXEC sp_configure 'xp_cmdshell', 1
　　GO
　　--重新配置
　　RECONFIGURE
　　GO

本文采用的第一种方法。

经过实验，可以在本机（192.168.1.10）上实现对数据库的备份。但是存储到目标计算机（192.168.1.20）失败，结果显示为没有权限。

分析问题的原因应该是出现在“exec Master..xp_cmdshell @SQLStr --拷贝到目标机器上 ”语句上。

利用命令提示符可以将备份文件复制到"//192.168.1.20/SharedDocs/"文件夹下，于是想到是SQL Server 权限的问题。

网上搜索得到解决方法：

权限配置的问题,参考下面的设置来做映射(假设A服务器上的数据库备份到B服务器)(限局域网内)::

1.机器A,B创建一个同名的windows用户,用户组设置为administrators,并设置相同的密码,做为备份文件夹文件夹的有效访问用户,操作:
我的电脑
--控制面板
--管理工具
--计算机管理
--用户和组
--右键用户
--新建用户
--建立一个隶属于administrator组的登陆windows的用户

2.在B机器器上,新建一个共享目录,做为备份文件的存放目录,操作:
我的电脑--D:/   新建一个目录,名为:   BAK
--右键这个新建的目录
--属性--共享
--选择"共享该文件夹"
--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限
--确定


3.设置   MSSQLSERVER   及   SQLSERVERAGENT   服务的启动用户
开始--程序--管理工具--服务
--右键   MSSQLSERVER
--属性--登陆--选择"此账户"
--输入或者选择第一步中创建的windows登录用户名
--"密码"中输入该用户的密码
--确定
--同样的方法设置   SQLSERVERAGENT

4.在A机器上完成对B机器BAK目录的映射

5.查询分析器中执行下面的语句,检验是否成功:
      exec   master..xp_cmdshell   'dir   映射的盘符'