November 11, 2010 at 3:40 am
On one of our test servers we backup our SQL databases to a local drive and then copy them to a network drive. The backups occur at 1am in the morning and the batch file at 1.40am, scheduled by sql agent, copies them to the network drive. For quite a number of nights the scheduled job to copy the backup files over to the network share have failed overnight. If I start the same job manually in the morning then the job runs fine.
The batch file is as follows
@echo off
echo ############################################################################# #
echo # #
echo # Name : Backup SQL BACKUPS to Hyperion drive #
echo # Date : 01 Nov 2010 #
#####################################################################
@echo on
REM ===============================================================
REM = COPY backup ups to (T -drive)
REM ===============================================================
echo %date%
set year=%date:~9,4%
set month=%date:~6,2%
set day=%date:~3,2%
echo %year%
echo %month%
echo %day%
echo %Time%
set hours=%time:~0,2%
set minutes=%time:~3,2%
set seconds=%time:~6,2%
echo %hours%
echo %minutes%
echo %seconds%
Echo on
net use t: \\10.35.8.273\Hyprion$ /user:HypMDD Amsterdan01
MD T:\DDM_Backup\SQLBackup_%year%_%month%_%day%_%hours%_%minutes%_%seconds%
move "E:\Microsoft SQL Server\MSSQL$SQL2000\Backup\*.*" T:\DDM_Backup\SQLBackup_%year%_%month%_%day%_%hours%_%minutes%_%seconds%
The error in the log file is
C:\WINDOWS\system32>REM ===============================================================
C:\WINDOWS\system32>REM = COPY backup ups to (T -drive)
C:\WINDOWS\system32>REM ===============================================================
C:\WINDOWS\system32>echo di 09-11-2010
di 09-11-2010
C:\WINDOWS\system32>set year=2010
C:\WINDOWS\system32>set month=11
C:\WINDOWS\system32>set day=09
C:\WINDOWS\system32>echo 2010
2010
C:\WINDOWS\system32>echo 11
11
C:\WINDOWS\system32>echo 09
09
C:\WINDOWS\system32>echo 1:20:00,80
1:20:00,80
C:\WINDOWS\system32>set hours= 1
C:\WINDOWS\system32>set minutes=20
C:\WINDOWS\system32>set seconds=00
C:\WINDOWS\system32>echo 1
1
C:\WINDOWS\system32>echo 20
20
C:\WINDOWS\system32>echo 00
00
C:\WINDOWS\system32>Echo on
C:\WINDOWS\system32>net use \\10.35.8.273\Hyprion$ /user:HypMDD Amsterdan01
System error 85 has occurred.
The local device name is already in use.
C:\WINDOWS\system32>MD T:\DDM_Backup\SQLBackup_2010_11_09_ 1_20_00
C:\WINDOWS\system32>move "E:\Microsoft SQL Server\MSSQL$SQL2000\Backup\*.*" T:\DDM_Backup\SQLBackup_2010_11_09_ 1_20_00
The syntax of the command is incorrect.
Not sure why it is doing this. Any help would be very much appreciated
November 11, 2010 at 6:38 am
It's saying you already have T: assigned as a drive name, and then failing because of that.
First, I'm not sure if you meant to, but it looks to me like you just posted a domain name and password for your system on a public forum. That's not a good idea. At least edit the post, though that won't take it out of Google's cache if it's already in there.
Second, it sounds like what you should have is a job with two steps, instead of two jobs. It sounds like you have two jobs. If the first one hasn't finished and the second one starts, that might cause a failure. In that case, you might end up with the net use drive name not being deallocated (I'm not sure about that, but it seems to make sense), and then future attempts at the net use command would fail.
Net use can explicitly close a connection if you use the /DELETE option. You might want to try that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2010 at 6:52 am
Hi,
I edited the domain details and username etc beforehand.
I think your suggestion might be correct. I have looked at the history and the time for the backups to complete varies from 16min to 45 mins. I will put 2 steps in one job and let you know how it goes.
Many Thanks for your suggestion.
November 11, 2010 at 1:14 pm
eseosaoregie (11/11/2010)
...I edited the domain details and username etc beforehand....
Good. Had me worried for a bit there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2010 at 2:16 am
I tried doing the backup the job in 2 steps. i.e first step to do the backup and the second step to run a batch job to copy the backups over to the network drive. However it still fails
The batch job first creates a folder with a date timestamp inbedded in the name. Currently the folder is created but the naming convention is incorrect as it doesn't append the time part to the folder.
the error message is as follows:
c:\WINDOWS\system32> MD T:\DDM_Backup\SQLBackup_2010_11_11_ 9_46_46
A subdirectory or file T:\DDM_Backup\SQLBackup_2010_11_11 already exists
Error occurred while processing: T:\DDM_Backup\SQLBackup_2010_11_11
Not sure what is happening here because as before if I run the job manually it works.
Any help would be appreciated/.
November 18, 2010 at 10:57 am
This might be silly but the command: MD T:\DDM_Backup\SQLBackup_2010_11_11_ 9_46_46
has a space in it between the date and the time. Unless the path is encapsulated by quotation marks would it create the directory as you stated? or would it just make:
T:\DDM_Backup\SQLBackup_2010_11_11_
Thanks
Epikto
Regards,
Michael Clement, MCTS
November 19, 2010 at 2:17 am
Its not silly at all lol. Indeed I put quotation marks around the command and it ran perfectly via the sql job scheduler. What I dont understand is that if there are no quotation marks and I run the job manually it works. If its is scheduled and there are no quotation marks a space is generated in the time stamp. Very strange? Suffice to say I just need the job to work so I put quotation marks there.
Thanks for your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply