Taking backup of Database on Remote System for hourly basis

Dear All

Default SQL server will not allow to take backup on remote system but now We can schedule SQL Server database backup on Hourly Basis by following SQL scripts

Follwoing script can be used to schedule Full database backup on remote system on daily basis

‘******************************************************************

Need to change Ip of remote system and Database name

‘*****************************************************************

declare @day_week int,@directory char(80)
set @day_week = datepart(dw,getdate())
if @day_week = 1
begin
set @directory = ‘\\10.24.230.182\Full\day1\db_Full.bak’
end
else if @day_week = 2
begin
set @directory = ‘\\10.24.230.182\Full\day2\db_Full.bak’
end
else if @day_week = 3
begin
set @directory = ‘\\10.24.230.182\Full\day3\db_Full.bak’
end
else if @day_week = 4
begin
set @directory = ‘\\10.24.230.182\Full\day4\db_Full.bak’
end
else if @day_week = 5
begin
set @directory = ‘\\10.24.230.182\Full\day5\db_Full.bak’
end
else if @day_week = 6
begin
set @directory = ‘\\10.24.230.182\Full\day6\db_Full.bak’
end
else if @day_week = 7
begin
set @directory = ‘\\10.24.230.182\Full\day7\db_Full.bak’
end
BACKUP DATABASE [JRADM] TO DISK = @directory WITH INIT , NOUNLOAD , NAME = N’db_Full’, SKIP , STATS = 10,  NOFORMAT

‘*****************************************************************

Follwoing SQL script can be used to schedule teh hourly Differencial backup of database in sepated folders.

‘*******************************************************************

Need to change Ip of remote system and Database name

‘*******************************************************************

declare @day_week int,@directory char(80),@tt int
set @day_week = datepart(dw,getdate())
set @tt = datepart(hh,getdate())
if @day_week = 1
begin
set @directory = ‘\\10.24.230.182\Diff\day1\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
else if @day_week = 2
begin
set @directory = ‘\\10.24.230.182\Diff\day2\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
else if @day_week = 3
begin
set @directory = ‘\\10.24.230.182\Diff\day3\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
else if @day_week = 4
begin
set @directory = ‘\\10.24.230.182\Diff\day4\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
else if @day_week = 5
begin
set @directory = ‘\\10.24.230.182\Diff\day5\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
else if @day_week = 6
begin
set @directory = ‘\\10.24.230.182\Diff\day6\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
else if @day_week = 7
begin
set @directory = ‘\\10.24.230.182\Diff\day7\’+convert(varchar(2),@tt) +’\db_1stdiff.bak’
end
BACKUP DATABASE [JRADM] TO DISK = @directory WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N’db_diff’, SKIP , STATS = 10, DESCRIPTION = N’db differential backup’, NOFORMAT

‘***************************************************************

and please download follwoing Folder structure and pate on the remote system and share it

Then Schedule the SQl job in Enterprise Manager of SQL Server 2000 on hourly basis and daily as per the Scripts.


You can see the Video Tutorial for Practical Demonstration

Feel free to ask any kind of doubt

Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s