Hi All,

I have two environment PA and DEV.  both are in different domain I just want to automated a process of backup database in PA and
restore it on dev server .
No mattered I can automate backup job in PA and restore job in DEV but

How should copy backup file automatically from PA to DEV?

Normally I right click on the backup file and copy and directly past into Dev server it works but i want automated.

There is not any shared location.

• Edited by ImranKazi Friday, July 10, 2015 9:46 AM

Hi Imran,

if you could reach the Server by IP Adresse you could make something like that for destination

\\<IPAddress>\<d$>\<backup>

And if you do not want to use the IP Address make a alias at your Domain with this IP Address.

Regards

Marius

In one of the environments I manage, I use the following script to perform a database backup restore test automatically and it works well. You need to assign relevant details where you see labels between <>...

Note: I execute this script via a SQL Server Agent Job at the target server.

DECLARE @path varchar(1024)
DECLARE @dbname varchar(100)
DECLARE @dbname_tobe_restored varchar(100)
DECLARE @backupdate datetime
DECLARE @copycmd nvarchar(1000)

-- This query is run via a linked server at the remote server against the server that the backups are taken.

-- Find a Full database backup file that is one month old.

SELECT TOP 1 @dbname = bs.database_name, @backupdate = bs.backup_start_date, @dbname_tobe_restored = replace(RIGHT([physical_device_name], CHARINDEX('\', REVERSE([physical_device_name]))-1), '.bak', ''), @path = bmf.physical_device_name FROM [<source_server_linkedServer>].[msdb].[dbo].[backupset] bs INNER JOIN [<source_server_linkedServer>].[msdb].[dbo].[backupmediafamily] bmf ON bs.media_set_id=bmf.media_set_id WHERE bs.database_name = '<db_name_to_be_copied>' AND bs.type = 'D' AND bs.backup_start_date BETWEEN DATEADD(WK, -1, GETDATE()) AND GETDATE() ORDER BY bs.backup_start_date DESC

SELECT @copycmd = 'net use "\\<share_path>" /user:<user_name> <password> & copy \\<source_shared_path_for_backup_file>\' + @dbname_tobe_restored + '.bak <destination_path>\' + @dbname_tobe_restored + '.bak'

EXEC xp_cmdshell @copycmd -- copies the backup file

-- Create the script to restore the file

SELECT @copycmd = 'IF EXISTS(SELECT name FROM sys.databases WHERE name = ''<db_name_to_overwrite>'') BEGIN ALTER DATABASE [<db_name_to_overwrite>] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; END; RESTORE DATABASE [<db_name_to_overwrite>] FROM  DISK = N''<backup_file_path>\' + @dbname_tobe_restored + '.bak'' WITH  FILE = 1,  MOVE N''<logical_data_file_name>'' TO N''<full_data_file_path_including_file_name>'',  MOVE N''<logical_log_file_name>'' TO N''<full_data_file_path_including_log_name>'',  NOUNLOAD,  REPLACE,  CHECKSUM, STATS = 5'

EXEC sp_executesql @copycmd

Hi Imran,

do you have any Firewall between the two Domains?

The Port 445 Need to be opened.

Telnet <IPAddress> 445, to check if you have any port Problem.

And you also Need the rights to contact the admin share.

So please use the net use command first to connect to the share and then copy the files

Regards

Marius