如何在sql server中动态更改备份位置 [英] How to change backup location dynamically in sql server

问题描述

要求:由于一些空间问题，我们计划的作业失败了，所以为了避免这种情况，我开发了如果 FreeSpace 大于 50 GB 然后备份到 F 驱动器，如果 FreeSpace 小于50然后备份到G盘..它必须检查所有驱动器并自动更改路径.以下代码无法正常工作的一些原因.对此高度赞赏的帮助.

Requirement : Due to some space issue our scheduled jobs are failing so to avoid this I have developed if FreeSpace is >50 GB then backup to F drive , If FreeSpace is < 50 then backup to G drive .. it has to check the all the drives and change the path automatically. Some how the below code is not working.Help on this highly appriciated.

create table #Space(Drive varchar(4),SpaceAvailable varchar(15))
insert into #Space(Drive,SpaceAvailable)
exec xp_fixeddrives 

Alter table #Space add FinalSpace as spaceavailable/1024
select * from #Space

Declare @FreeSpace varchar(10)
select @FreeSpace=(select top 1 FinalSpace  from #Space)
print @FreeSpace
if (@FreeSpace <50)
begin
backup database PerfDB to disk='G:\PerfDB.bak'
end
if (@FreeSpace >50 )
begin
backup database PerfDB to disk='F:\PerfDB.bak'
end

推荐答案

以下代码对我有用.

create table #Space(Drive varchar(4),SpaceAvailable varchar(15))
insert into #Space(Drive,SpaceAvailable)
exec xp_fixeddrives 

Alter table #Space add FinalSpace as spaceavailable/1024
select * from #Space

Declare @FreeSpace int=0
select @FreeSpace=51 --(select top 1 FinalSpace  from #Space)
print @FreeSpace
if (@FreeSpace between 0 and 50)
begin
print 'No Space Available'
end
if (@FreeSpace between 51 and 124  )

begin
backup database PerfDB to disk='E:\PerfDB.bak'
Print 'Loop Entered to E'
end
if (@FreeSpace  between 125 and 150  )
begin

backup database PerfDB to disk='F:\PerfDB.bak'
Print 'Loop Entered to F'
end

这篇关于如何在sql server中动态更改备份位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！