sqlserver 2005 数据库异机备份


思路 :在数据库上创建存储过程,然后建立作业定时备份。
       为了防止直接向远程备份数据库网络中断,还是先备份到本地,再拷到远程硬盘的好,用SQL的脚本可以调用master..cmdshell \'copy...\'实现。
存储过程代码如下:
use master
go
create proc 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.102\Administrator' --目标机器域名\目标机器windows用户名
Set @strPsw='sdjz'--目标机器Windows登陆密码
Set @strCmdShell= 'net use \\192.168.1.102\ipc$ ' + @strPsw + ' /user:' +@strUsr --需填写IP(目标机器的IP地址,如:192.168.2.178)
Set @strDataBaseName='strs'--填写数据库名称(如:Soondy)
Set @FullFileName='E:\mq\'+@FileFlag+'.BAK'--需填写本地备份临时文件的目录,因为需要先备份到本地再copy到目标机器(如:保存目录为E:\SoondyTest\)
Set @ToFileName='\\192.168.1.102\97sqlserver_bak\' --需填写IP(目标机器的IP地址)以及保存的目录(如:\\192.168.1.140\db\)
Set @FlagDel='True'--填写True表示删除本地的备份临时文件,填写False或其他字符表示保留该文件
Set @SQLStr='copy '+@FullFileName+' '+@ToFileName 
Set @SQLStr2='del ' +@FullFileName
--备份;with init覆盖 |noinit添加 
BackUp DataBase @strDataBaseName To Disk= @FullFileName   with init

PRINT @strCmdShell
PRINT @SQLStr
exec master..xp_cmdshell @strCmdShell--尝试连接到目标机器
exec Master..xp_cmdshell @SQLStr --拷贝到目标机器上 
if (@FlagDel ='True') exec master.. xp_cmdshell @SQLStr2--删除本地的备份临时文件
GO	

出现的错误 和解决办法

1、SQL Server 阻止了对组件'xp_cmdshell' 的过程'sys.xp_cmdshell' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'xp_cmdshell'。有关启用'xp_cmdshell' 的详细信息,请参阅SQL Server 联机丛书中的"外围应用配置器"。

启动外围应用配置器工具
开始菜单中,依次指向程序、“Microsoft SQL Server 2005配置工具,再单击“SQL Server 外围应用配置器。单击配置外围应用旁边的链接。默认值为 localhost。如果您以前选择的是一个命名服务器,则将会看到该服务器名称。选择“功能的外围应用配置器”,启用'xp_cmdshell'选项打勾即可。
如果是sqlserver2008 运行以下sql代码 
由默认的0变成1 

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO



2、net use ipc$错误解决(发生系统错误 67)
2009-06-25 14:31

net use ipc$错误解决

发生系统错误 67。

找不到网络名

===============

解决:运行serivces.msc(即服务管理)

开启:

1.Server服务(显示名称)(对应名称为:lanmanserver)

2.Workstation服务(显示名称)(对应名称为:lanmanWorkstation)




*************************************补充 远程和本地只保留7天内的备份数据 存储过程如下*******************************

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create proc [dbo].[bakup_DataBase1]
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)
Declare @SQLStr3 varchar(500)
--同机备份如下
--填写数据库名称(如:Soondy)
Set @strDataBaseName='strsbak'
--备份的文件命名规则:数据库名20131213.bak
Set @FileFlag=@strDataBaseName+rtrim(convert(varchar,getdate(),112))  
Set @FullFileName='E:\mq\'+@FileFlag+'.BAK'--需填写本地备份临时文件的目录,因为需要先备份到本地再copy到目标机器(如:保存目录为E:\SoondyTest\)   
---异机备份如下
Set @strUsr='192.168.1.102\Administrator' --目标机器域名\目标机器windows用户名
Set @strPsw='sdjz'--目标机器Windows登陆密码
Set @strCmdShell= 'net use \\192.168.1.102\ipc$ ' + @strPsw + ' /user:' +@strUsr --需填写IP(目标机器的IP地址,如:192.168.2.178)
Set @ToFileName='\\192.168.1.102\97sqlserver_bak\' --需填写IP(目标机器的IP地址)以及保存的目录(如:\\192.168.1.140\db\)
Set @SQLStr='copy '+@FullFileName+' '+@ToFileName ---向异机copy

Set @FlagDel='True'--填写True表示删除本地的备份临时文件,填写False或其他字符表示保留该文件
--保留7天以内的备份
Set @SQLStr2='del '+'e:\mq\'+@strDataBaseName+rtrim(convert(varchar,getdate()-7,112))+'.BAK'
Set @SQLStr3='del '+@ToFileName+@strDataBaseName+rtrim(convert(varchar,getdate()-7,112))+'.BAK'
BackUp DataBase @strDataBaseName To Disk= @FullFileName   with init

PRINT @strCmdShell
PRINT @SQLStr

exec master..xp_cmdshell @strCmdShell--尝试连接到目标机器
exec Master..xp_cmdshell @SQLStr --拷贝到目标机器上 
if @FlagDel ='True'
begin
PRINT @SQLStr2
exec master.. xp_cmdshell @SQLStr2--删除本地的备份7天以外的备份文件
exec master.. xp_cmdshell @SQLStr3--删除远程的备份7天以外的备份文件
PRINT @SQLStr3
end


没有更多推荐了,返回首页