# Script to Copy Data



## bluem (Mar 1, 2006)

Hello,

I am looking for a script (.Bat) file to copy data from a directory on server A to another directory on server B.

For example copy the entire contens for MSSQL folder on the C Drive "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL" TO another direcotry called "SQL" on server with the following address 1.1.1.1 as an example.

Can someone assist?

Thanks,
b


----------



## Codiah (Sep 27, 2008)

Batch scripting automates task's so you need to have all the tools for the task... You can easy use Batch to automat it, but I would suggest using a small FTP server set to run as a background process on the server compueter...

then it would be as simple as using a batch and .DLL file to automate the entire task at the click of a button....

So, really my first question is does your server B have FTP access... if not i would suggest using a small program such as my favorite, *cerberus*... or using *FileZilla* which is free...

so get back to us with a response on that, because if you don't use an FTP server many of us here can probably help yopu set it up to be secure and working properly...


----------



## bluem (Mar 1, 2006)

The reason I need a script is because i have SQL 2005 running on Windows 2008 Server and I created within SQL a job to back the SQL Databse to a folder. My only concern is to get the backup files copied over to network server incase the SQL Server crashes. I would have used the Windows Backup Service but in Windows 2008 you have to back the entire drive or nothing. Backing up selected folder only available in Windows 2003.

Thanks,
b


----------



## Rollin_Again (Sep 4, 2003)

You can use a VBS script and Windows Task Scheduler to do this also. Have you mapped the destination drive/directory?

Regards,
Rollin


----------



## Codiah (Sep 27, 2008)

Would VBScript allow you to transfer files over a network??

perhaps i might learn something out of this thread..


----------



## TheOutcaste (Aug 8, 2007)

*Xcopy /CDHIKSRXZ "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL" \\1.1.1.1\SQL*
Copies all files and folders except empty ones
Will not copy files that already exist and have not been modified
Copies attributes, permissions, and audit settings
Copies Hidden and System files
Will overwrite read-only files
This will _*not*_ remove files that no longer exist, so you'd need to occasionally delete the destination files to keep old files from building up

You can schedule this as a scheduled task.

You can also use Robocopy which can be set to purge files that no longer exist

*Robocopy /S /Copyall /XO /Purge /R:10 /W:10 /Z* *"C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL" \\1.1.1.1\SQL*

This will also retry each file 10 times, waiting 10 seconds between retries. Handy if there could be interruptions in the network.
You can also use /MIR to mirror the directory tree. I haven't played with that option much.
Using /Purge or /Mir will delete files, so be sure to test first using the .L switch to list files, but not make any changes.

Robocopy can also be set to monitor the source folder and run again when more than a specified number of changes have occurred, or run on a timed basis only if there is a change, and you can set times it is allowed to run so it won't impact the network during business hours for example.

Or put it in a batch file and use scheduled tasks to run it.

HTH

Jerry


----------



## bluem (Mar 1, 2006)

Thanks for the reply....however when I pasted the script into a .bat file and ran it manually it did not work. (I did make changes to the server name).
Xcopy /CDHIKSRXZ "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL" \\1.1.1.1\SQL

Thanks,
b


----------



## TheOutcaste (Aug 8, 2007)

What error did you get?
Is the SQL folder on the server (1.1.1.1) shared?
The account you run this from must have permission to access the shared SQL Folder.

Jerry


----------



## bluem (Mar 1, 2006)

No error message. I pasted the code into a .bat file and double clicked to run it...I notice a window flashing and that is it. The folder on the network is shared and permissions are correct.

I am running the script in windows 2008 server.

Thanks,
b


----------



## TheOutcaste (Aug 8, 2007)

You need to open a Command Prompt first, then run the batch file. That way the window will stay open so you can see any error messages.

Jerry


----------



## bluem (Mar 1, 2006)

The problem seemed to be with "/CDHIKSRXZ" because when I remove it the copying works. I am not sure if this is related to Windows 2008 Server.


----------



## TheOutcaste (Aug 8, 2007)

They must have dropped one or more of the switches along the way.
Did it list all of them as Invalid Switch?

You may want to check help (*xcopy /?* in a Command Prompt)to see what switches are supported. If you have subfolders they might not be copied without using the /S or /E switch for example, and file permissions won't be copied without the /O or /X switch.

It may also need each switch to be specified separately, instead of all together like so:
*/C /D /H /I /K /S /R /X /Z*

I'll see if I can find anything specific to Xcopy on Win2K8

HTH

Jerry


----------



## Zack Barresse (Jul 25, 2004)

Codiah said:


> Would VBScript allow you to transfer files over a network??


As long as you have permissions to access that drive/network, you should be able to do it with VBS, yes. Just to answer your question.


----------



## TheOutcaste (Aug 8, 2007)

The Windows Server 2008 Command Line Reference for xcopy just says *This command has been deprecated. Please use Robocopy instead*

So unless the built in help lists the switches it would be a matter of trial and error to find which ones still work.

Jerry


----------



## bluem (Mar 1, 2006)

For some reaons the /x seemed to disable the xcopy.

I was able to get some files copied using /c /h /y /e /k /i /r but some files were not copied. The files that were not copies are SQL Database files, could this because they are in use?

Thanks,
b


----------



## TheOutcaste (Aug 8, 2007)

Guess it doesn't like copying Audit info. Wonder if /O also fails.
And xcopy can't copy files in use, you'll get a sharing violation, or access denied error.
Robocopy can't copy files that are in use either.

What you'd have to do is create a Volume Shadow copy, mount the shadow copy, then backup from the shadow copy using xcopy or robocopy.

I've never used the Volume Shadow Service so won't be much help. Not sure how much free space would be required either.
Google for vshadow.exe and Volume Shadow Service.
Here's one link that might help:
http://www.eggheadcafe.com/tutorial...c-36196a82ed25/workstation-open-file-bac.aspx

Jerry


----------

