Transfer of backup files | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transfer of backup files

Hi, I have a scenario where a scheduled maintenance is made to run daily once, within the schedule maint. is the full & periodic transaction backup for various database. Now i want to have a confirmation whether is it possible to take a backup directly on a different server (DR Server) via LAN network, if not then is it possible to run either a auto scheduled job process on the main server whereby these back files can be automatically transferred from the main server to the DR server or else a auto scheduled batch process which can do the same process, if any one has the script then your help will be greatly appreciated. Thanks Bhushan
You can backup directly to a remote server. but I would recommand you do it localy than copy the file with a simple copy command executed with xp_cmdshell. Bambola.
Can u plzz tell me the steps to backup directly to remote servers, as thru the EM there is no option for network so there is no access for the remote server. Thanks Bhushan
I agree with Bambola. Having backup on the local machine will eliminate any network latency issues and SQL Server will have minimal downtime (as the backup time may be more when done over network). You can copy the backup as a part of the job using xp_cmdshell as Bambola said. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
DECLARE @BackupName varchar(50)
DECLARE @SourceFile varchar(255), @TargetFile varchar(255), @Action varchar(255) SELECT @BackupName = rtrim(‘DBBackup0’ + convert(char,(select datepart(dw,getdate()) – 0) ))
select @BackupName
BACKUP DATABASE DBName TO @BackupName WITH INIT
Select @SourceFile = ‘\Servernamepath$’+rtrim(@BackupName)+’.bak’
Select @TargetFile = ‘\Servernamepath$’+rtrim(@BackupName)+’.bak’
Select @Action = ‘Copy ‘+rtrim(@SourceFile)+’ ‘+rtrim(@TargetFile)
EXEC master..xp_cmdshell @Action
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
That’s nice Satya. Bhushan, you can go ahead and add error handling if you want to. FYI, XP_CMDSHELL does return a value to indicate if the command execution was a success or not. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
If not you can set the alert notification from the job itself. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Satya I think sending notification is dependent and not exclusive of catching the error. Or is it the case that the error will be caught during the execution of the job no matter what? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
THe outcome of job is notified with alert irrespective of its result, I feel thats better to be alerted. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hi Friends, Thank you all for your help, i think using xp_cmdshell ESP as a daily scheduled job will help me automate my transfer of backup files from main server to backup or DR server. Thanks Bhushan
]]>