Link to home
Start Free TrialLog in
Avatar of Deepak Kumar
Deepak KumarFlag for San Marino

asked on

Need a batch script for copying SQL backup files(.bak) from one server to another server in same Network

Hi folks,
   could you help me in providing a batch script file which allows me to copy sql server backup files which are with the .bak extension from machine1 to another machine in a network.

Please note: The drives are not network shared.

I may need to put this batch file in a scheduled task with fixed intervals.

thank you in advance!

Regards
Deepak
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,

Pls try ..

--

xcopy /e c:\YourSourceDirectory\*.bak \\NetworkDrive\

--

Open in new window


Hope it helps !
Avatar of NVIT
You title says: Need a batch script for copying SQL backup files(.bak) from one server to another server in same Network

Then you say: The drives are not network shared.

This doesn't make sense.

If you are the admin on the same workgroup or domain, you should be able to access each server via the network.
Yes, the machine performing the copy needs to have access to both locations, of course.

Do you want to get an exact mirror  - the destination looks the same as the source, old files get deleted -  or an copy  - files are only added to the destination, so you'll have to take care for yourself to always have enough free space?
Avatar of Deepak Kumar

ASKER

sorry for the note, yes the drive can be shared. Do we need to specify on which user authentication the scripts will get executed?

thanks
Deepak
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Pawan, that's wrong and too simple.

Deepak, works you please answer my questions?
The user running the script should have proper network access, of course. Scripts running as scheduled task or part of another process should use UNC paths, as non-local drive letters are not available there.
Pawan, when i run the script manually, yes it copies the files from my machine 1 to machine 2. not checked with the scheduled task yet.

Qlemo,
  I don't want the exact source and destination mirror . Basically why i wan't to copy the files to  machine2 where already few sql backups of machine2 are stored is , there is a windows team who backups the sql backup files of machine2 to tape, hence we wanted the sql backup files of machine1 also to be backed up in tape using the same process which the tape team currently performs only for machine1.

Pawan,Qlemo,
Still have a question when i put it under scheduled task which user permission will it take it to run on fixed intervals? I am much likely to run this scheduled task under task or service account so that if in future i move out from team, nothing should go wrong with the copying files.

Apart from this, can we also have some script in addition which performs the cleanup in the destination folder where more than 2 weeks of backup files should be deleted based on modified date or file creation date of backup files?

Kindly provide your thoughts or suggest me best solution to implement on this.

Thank you for both of you!!

Regards
Deepak
Hi,

Still have a question when i put it under scheduled task which user permission will it take it to run on fixed intervals?
>> For this you need <<Logon as a batch job>>

Apart from this, can we also have some script in addition which performs the cleanup in the destination folder where more than 2 weeks of backup files should be deleted based on modified date or file creation date of backup files?
>> Try like below (I have used 14 days for now - you can choose the number)

 
--
forfiles -p "YourPath" -m *.bak -d -14 -c "cmd /c echo
--

Open in new window


Hope it helps
Pawan,
   I get the following error when i run the script for cleaning up. I have run this script from the machine1 and the path section has been referrenced to \\servername\foldername\

ERROR: UNC paths (\\machine\share) are not supported.

Could you tell me, is there something i did ?

thanks
Deepak
@Deepak,

If you give some specifics, for example, the source and target folders, we can probably tailor a script  to your need.

Does the source include just that source's single folder? Or, does it include it's sub-folders, too?

Once we have that, you can maybe use the XCOPY example given by Pawan. If the task is run on Server1, then maybe something like:
xcopy "c:\YourSQLSourceDirectory\*.bak" \\Server2\sharename\SQLBackupFolder

Open in new window


@Pawan,

I think you mean to echo @PATH
forfiles -p "YourPath" -m *.bak -d -14 -c "cmd /c echo @PATH"

Open in new window


https://technet.microsoft.com/en-us/library/cc753551(v=ws.11).aspx
> ERROR: UNC paths (\\machine\share) are not supported.
You'll need to map a drive letter. Then feed the drive letter to forfiles

net use v: \\machine\share
forfiles ...

Open in new window


Or, a PUSHD and POPD between forfiles may work
pushd
forfiles ...
popd

Open in new window


Of course, the above assumes the account/user running forfiles must have access to the UNC source.
Hi Deepak,

UNC paths are not supported. @NVIT - Thank you very much for the help. :)

Deepak I thought you will create the scheduler with the task directly on server itself.  Anyway you can use pushd and popd.  Try like..

set "loc=\\Server\Location" 
pushd "%loc%"
for /d %%i in (*.bak) do del "%%i" /s /q 
popd

Open in new window


Hope it helps !
Hi Pawal,
  I am ok with the first script which will run in the same server but when i run it, my backup files are not getting deleted.
My path is S:\BatchScriptDB.

thanks
Deepak
Pawan, https:#a41928020 is utter nonsense and potentially dangerous.  There is no date check, the for acts on folders, ...
Qlemo - Thank you for your comment.!.  The Author is giving me the reply whether it is working or no. You don't have to do that. Thnx.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial