MSSQL的异地远程存储,SQLServer备到FileServer上
环境: win2k+sqlserver 2K SP3
环境: win2k+sqlserver 2K SP3
backup database msdb to disk=’computer estfilename.bak’ --(注意大小写)
如果SQL异地备份失败,归根结底是权限问题! H
那么你的SQLServer的启动用户必须在FileServer上有足够的权限!
1、SQLServer上新建一SQLUser用户权限大一点。
2、FileServer上建同一用户对某一文件夹有足够权限,就是在两台机器上建相同的用户名和密码,然后与这个用户名登入电脑。
3、两机的SQLUser密码相同(方便一点)
4、将SQLServer改为SQLUser启动
(管理工具-->服务-->mssql项-->属性-->指定用户及密码,是计算机的登入用户名)
5、backup database 数据库 to disk=’192.168.*.*文件夹ShareBak.Bak’就可以了。
作业:db_backup2pc @databaseName='databaseName',@filepath='filepath'
CREATE
PROCEDURE
dbo.db_backup2pc
@databaseName nvarchar ( 100 ) = null , -- 数据库名
@filepath nvarchar ( 125 ) = null -- 文件保存路径(IP+隐藏共享+)
AS
BEGIN
DECLARE
@year1 varchar ( 4 ),
@month1 varchar ( 2 ),
@day1 varchar ( 2 ),
@flag varchar ( 255 ),
@proc_result tinyint , /**/ /*返回系统存储过程xp_cmdshell运行结果*/
@sqlstr varchar ( 2000 ),
@createdir varchar ( 255 ) /**/ /*建立文件备份的目录*/
begin
-- Get year & month &day fromat of the day before yesterday
-- SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),3,2)
SET @month1 = substring ( convert ( varchar , datepart (mm, getdate ())), 1 , 2 )
SET @day1 = substring ( convert ( varchar , datepart (dd, getdate ())), 1 , 2 )
-- if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */
-- if len(@day1)<2 set @day1 = '0' + @day1
set @sqlstr = ' dir ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
EXEC @proc_result = master..xp_cmdshell @sqlstr ,no_output
if ( @proc_result <> 0 ) /**/ /*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/
begin
set @createdir = ' md ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 /**/ /*建立文件备份的目录*/
EXEC master..xp_cmdshell @createdir ,no_output
set @sqlstr = ' backup database ' + @databaseName + ' to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' ' + @databaseName + ' .bak ''' -- +' with init'
Execute ( @sqlstr ) /**/ /* 备份databaseName数据 */
set @sqlstr = ' backup database master to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' master.bak '''
Execute ( @sqlstr ) /**/ /* 备份 master 数据 */
set @sqlstr = ' backup database msdb to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' msdb.bak '''
Execute ( @sqlstr ) /**/ /* 备份 msdb 数据 */
set @flag = ' Backup database successful. '
end
else
set @flag = ' The directory " ' + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' " has been in, backup database unsuccessful. '
print @flag
END
END
GO
@databaseName nvarchar ( 100 ) = null , -- 数据库名
@filepath nvarchar ( 125 ) = null -- 文件保存路径(IP+隐藏共享+)
AS
BEGIN
DECLARE
@year1 varchar ( 4 ),
@month1 varchar ( 2 ),
@day1 varchar ( 2 ),
@flag varchar ( 255 ),
@proc_result tinyint , /**/ /*返回系统存储过程xp_cmdshell运行结果*/
@sqlstr varchar ( 2000 ),
@createdir varchar ( 255 ) /**/ /*建立文件备份的目录*/
begin
-- Get year & month &day fromat of the day before yesterday
-- SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),3,2)
SET @month1 = substring ( convert ( varchar , datepart (mm, getdate ())), 1 , 2 )
SET @day1 = substring ( convert ( varchar , datepart (dd, getdate ())), 1 , 2 )
-- if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */
-- if len(@day1)<2 set @day1 = '0' + @day1
set @sqlstr = ' dir ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
EXEC @proc_result = master..xp_cmdshell @sqlstr ,no_output
if ( @proc_result <> 0 ) /**/ /*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/
begin
set @createdir = ' md ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 /**/ /*建立文件备份的目录*/
EXEC master..xp_cmdshell @createdir ,no_output
set @sqlstr = ' backup database ' + @databaseName + ' to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' ' + @databaseName + ' .bak ''' -- +' with init'
Execute ( @sqlstr ) /**/ /* 备份databaseName数据 */
set @sqlstr = ' backup database master to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' master.bak '''
Execute ( @sqlstr ) /**/ /* 备份 master 数据 */
set @sqlstr = ' backup database msdb to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' msdb.bak '''
Execute ( @sqlstr ) /**/ /* 备份 msdb 数据 */
set @flag = ' Backup database successful. '
end
else
set @flag = ' The directory " ' + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' " has been in, backup database unsuccessful. '
print @flag
END
END
GO
作业:delete_db_backupfile2pc @databaseName='databaseName',@filepath='filepath'
CREATE
PROCEDURE
dbo.delete_db_backupfile2pc
@databaseName nvarchar ( 100 ) = null ,
@filepath nvarchar ( 125 ) = null
AS
DECLARE
@year1 varchar ( 4 ),
@month1 varchar ( 2 ),
@day1 varchar ( 2 ),
@sqlstr varchar ( 2000 ),
@flag varchar ( 255 ),
@proc_result tinyint /**/ /*返回系统存储过程xp_cmdshell运行结果*/
begin
if @databaseName = null or @filepath = null
set @flag = ' Variable Error! '
else
begin
-- 保存7天的数据
SET @month1 = substring ( convert ( varchar , datepart (mm, getdate () - 7 )), 1 , 2 )
SET @day1 = substring ( convert ( varchar , datepart (dd, getdate () - 7 )), 1 , 2 )
-- if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */
-- if len(@day1)<2 set @day1 = '0' + @day1
set @sqlstr = ' dir ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
EXEC @proc_result = master..xp_cmdshell @sqlstr ,no_output
if ( @proc_result = 0 ) /**/ /*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/
begin
set @sqlstr = ' del ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' /q '
exec master..xp_cmdshell @sqlstr ,no_output -- 删除目录下的文件
set @sqlstr = ' rd ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
exec master..xp_cmdshell @sqlstr ,no_output -- 删除目录
set @flag = ' Delete file successful! '
end
else
set @flag = ' Can not find " ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' " file! '
end
print @flag
end
GO
@databaseName nvarchar ( 100 ) = null ,
@filepath nvarchar ( 125 ) = null
AS
DECLARE
@year1 varchar ( 4 ),
@month1 varchar ( 2 ),
@day1 varchar ( 2 ),
@sqlstr varchar ( 2000 ),
@flag varchar ( 255 ),
@proc_result tinyint /**/ /*返回系统存储过程xp_cmdshell运行结果*/
begin
if @databaseName = null or @filepath = null
set @flag = ' Variable Error! '
else
begin
-- 保存7天的数据
SET @month1 = substring ( convert ( varchar , datepart (mm, getdate () - 7 )), 1 , 2 )
SET @day1 = substring ( convert ( varchar , datepart (dd, getdate () - 7 )), 1 , 2 )
-- if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */
-- if len(@day1)<2 set @day1 = '0' + @day1
set @sqlstr = ' dir ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
EXEC @proc_result = master..xp_cmdshell @sqlstr ,no_output
if ( @proc_result = 0 ) /**/ /*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/
begin
set @sqlstr = ' del ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' /q '
exec master..xp_cmdshell @sqlstr ,no_output -- 删除目录下的文件
set @sqlstr = ' rd ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
exec master..xp_cmdshell @sqlstr ,no_output -- 删除目录
set @flag = ' Delete file successful! '
end
else
set @flag = ' Can not find " ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' " file! '
end
print @flag
end
GO