June 17, 2008 at 7:53 am
Hello Guys,
I have a bunch of backup .bak files and I need to copy them over the network to another remote server. I wanto create a job in sql server management studio and schedule it.I need to know a way where I can execute a batch file/vbs script or even schedule it. If you guys can give me code and also procedure, I mean what extension should file be saved and type of step to be used (Operating system (cmdexec) or active x script) it would be great.
Cheers,
Nikhil
June 17, 2008 at 8:20 am
If you use vbs you should use the active x script, i did have a script that did a similar thing but i have mis-placed it somewhere, if not google moving files with VB script and you'll some useful sites with example code. Its not too difficult.
Gethyn Elliswww.gethynellis.com
June 17, 2008 at 8:22 am
I do something similar, although it might be overkill for what you're trying to do. I have a batch job set up to zip up and encrypt all .bak files in a folder using WinZip command-line, delete the existing .bak files (so all my backups exist only as .zip files, with encryption), and then copy the newly-created .zip file to the remote location. This is all scheduled using the Windows Scheduler. Does this sound like something you could use? I'll post the batch file if it is.
June 17, 2008 at 8:28 am
I could use it your way, but can you give me the code and also tell me exactly how to do it???
Thanks,
Nikhil
June 17, 2008 at 8:40 am
I have the Z: drive mapped to the remote server. Also, the FOR statement works on Server 2003, but it would need to be changed if you're using Windows 2000. I don't remember exactly what needs to be changed to make it work with 2000, though.
[font="Courier New"]@echo off
for /f "tokens=2-4 delims=/ " %%a in ('DATE /T') do Set DateYMD=%%c%%a%%b
f:
cd "\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DatabaseName"
"C:\Program Files\WinZip\wzzip.exe" -sPassword -ee -yc BackupName_%DateYMD%_Backup.zip DatabaseName_backup*.bak
IF ERRORLEVEL 1 GOTO END
copy BackupName_%DateYMD%_Backup.zip Z:\Backup
del *.bak
:END[/font]
June 17, 2008 at 8:52 am
Thanks for the reply,
Thats a little too complicated for me, any1 has anything easier if I have to execute it in sql server mangement studio.
Nikhil
June 17, 2008 at 11:47 am
you can use the xp_cmdshell to copy/move a database from one location to another...syntax is
exec master..xp_cmdshell
Marvin Dillard
Senior Consultant
Claraview Inc
June 18, 2008 at 2:56 am
You are trying to copy the files over the network across servers.
I guess, we need to have the drives mapped accordingly otherwise it wouldn't be useful if we use xp_cmdshell
xp_cmdshell 'move Source_path destination_path'
or
xp_cmdshell 'copy source_path destination_path'
M&M
June 18, 2008 at 7:23 am
Why can't you go with Robo copy or network copy thru \\servername\drive $\path.
Or else use windows scheduler to use the copy over the network.
You can use Dos command as well. If the Backup is too large, probably you may need to take backup in tape and ship it asap.
MCP, MCTS (GDBA/EDA)
June 18, 2008 at 7:26 am
Check out this script. Break it down and it will do the backup and copy/move for you. IF you want a new version that also zips the file let me know.
"http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31932/"
Marvin Dillard
Senior Consultant
Claraview Inc
June 18, 2008 at 8:50 am
Nikhil,
Try this
xcopy \\source servername\D$\Backup\*.* \\Destination servername\E$\Backup\*.* /d /s
I haven't removed drive letters so as to avoid any confusion. Use drive letters and folder location according to server drives configuration.
Type of task--CMDEXEC
Also, make sure sql agent account on source server has read/write permissions on destination server drive.
Manu
June 18, 2008 at 10:20 am
I wouldn't use the default administrative (X$) shares for these types of operations. This means that your source server account has[/i] to be in the Administrators group on the target server. You may not always want your accounts to require admin level permissions on a target server...
Your friendly High-Tech Janitor... 🙂
June 18, 2008 at 2:45 pm
I just used a regular .BAT file executed in a Scheduled SQL Job.
Step 1, Backup the DB.
Step 2, Copy the .BAK to another location. Either using a batch file, or put the DOS copy command right in the SQL Job step. "Copy H:\Backups\MyBackup.BAK \\OtherServer\H\Backups\" Set the step type to "Operating System CmdExec"
Very simple
June 19, 2008 at 8:48 am
Thanks a tonn guys for all the help and the replies. I tried using it this way
This was the step which I created and gave type as cmdexec.
Copy C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak C:
Also I saved a copy.vbs file and then made a step with active x script and also cmd exec.
My copy.vbs was working fine when I was double clicking it but was not working from the sql server agent.
Finally, is there a way where in I can schedule the job outside sql server? I know that this is simple but I am not able to get it. Can any of you guys tell me the procedure with code as to how it can be done?
The process could not be created for step 14 of job 0x7AB659BDEB9F2A498F6CDAE858F10832 (reason: %1 is not a valid Win32 application). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
Any help would be gr888
Cheers
June 19, 2008 at 9:28 am
toparsi (6/19/2008)
Thanks a tonn guys for all the help and the replies. I tried using it this wayThis was the step which I created and gave type as cmdexec.
Copy C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak C:
Also I saved a copy.vbs file and then made a step with active x script and also cmd exec.
My copy.vbs was working fine when I was double clicking it but was not working from the sql server agent.
Finally, is there a way where in I can schedule the job outside sql server? I know that this is simple but I am not able to get it. Can any of you guys tell me the procedure with code as to how it can be done?
The process could not be created for step 14 of job 0x7AB659BDEB9F2A498F6CDAE858F10832 (reason: %1 is not a valid Win32 application). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
Any help would be gr888
Cheers
You are making dificult for yourself. It's versy easy to copy the file over the network. If you want regular copy of the backup then schedule the Windows schedular to copy from server to other server.
In that you can say Copy C:\programe files\MicrosoftSQL Server\MSSQL.1\Backup\DBname.bak \\Servername\D$\Backup
Put this command in SQL schedular and schedule it as you convinient time check the file manually.
If it is part of DR solution you may need to schedule the Job in SQL to restore the backup file as Logshipping will do.
MCP, MCTS (GDBA/EDA)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply