Backup

[jawan]

I have 2 servers lets say Server 'A' and Server 'B'
I want to make backup from Server 'A' and want to save on'B'
Is it possible...? how can i do it....?
What are the steps....?

Thanks

 

[cehepker]

Hello,

Here is the SQL that I use in a job to backup my databases.

USE [CrMaster]

DECLARE @File varchar(100)
DECLARE @DeleteFile varchar(100)
DECLARE @Date datetime
DECLARE @DeleteDate datetime

SET @Date = GETDATE()
SET @DeleteDate = GetDate() - 4

SET @File = 'D:\MSSQL7\BACKUP\CrMaster_db_'
+ CAST (DATEPART(yyyy, @Date)AS varchar)
+ RIGHT('0' + CAST (DATEPART(mm, @Date) AS varchar), 2)
+ RIGHT('0' + CAST (DATEPART(dd, @Date) AS varchar), 2)
+ RIGHT('0' + CAST (DATEPART(hh, @Date) AS varchar), 2)
+ RIGHT('0' + CAST (DATEPART(mi, @Date) AS varchar), 2)
+ '.bak'

BACKUP DATABASE [CrMaster] TO DISK = @File

-- Delete old backup files
SET @DeleteFile = 'Del D:\MSSQL7\BACKUP\CrMaster_db_'
+ CAST (DATEPART(yyyy, @DeleteDate)AS varchar)
+ RIGHT('0' + CAST (DATEPART(mm, @DeleteDate) AS varchar), 2)
+ RIGHT('0' + CAST (DATEPART(dd, @DeleteDate) AS varchar), 2)
+ '*.bak'

Use Master
EXEC xp_cmdshell @DeleteFile, no_output


In each section SET @File and SET @DeleteFile you can set the path to any other server, as long as the user running the script has authority to the other drive.

Hope this helps,
Carla

Documentation: A Shaft of light into a Coded world