August 4, 2009 at 10:40 pm
SQL Server 2005
Enterprise Edition 64-bit
SP3 9.0.4035
The default backup directory for this SQL instance is set to Q:\SQLBackups, i got this info from registry.
Now, if i run the below T-SQL via a SQL job
BACKUP DATABASE QAS
TO DISK ='Q:\SQLBackups\QAS\QAS.BAK'
the job fails with the following error:
Cannot open backup device 'Q:\SQLBackups\Q:\SQLBackups\QAS\QAS.BAK'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
It looks like SQL Server is appending the default directory path to the path that i specified in the SQL job and then trying to perform a backup.
Am i doing something wrong here? Shouldn't the SQL Server just take the path that i specified in the job?
Thanks,
Ravi
August 4, 2009 at 10:53 pm
Is the Q: drive a mapped drive or a local drive on the server? If a mapped drive to a network share, you may want to change the path to a local directory and then move or copy the backup file to the network location using the UNC instead of the mapped drive. You could also directly backup the database to the UNC, but I don't recommend that as any netowrk hiccup or glitch could cause the backup to fail as well.
August 4, 2009 at 11:02 pm
Q is a local drive.
August 4, 2009 at 11:48 pm
HI
Ravi,
There is some thing wrong with your backup command,because error is very clear and you mentioned a wrong file path.Just look in to error out put that says Cannot open following device.In your backup command you typed a Q: drive Twice time refer following Path ,Q: drive appeared twice time.So definately it will fail,
" 'Q:\SQLBackups\Q:\SQLBackups\QAS\QAS.BAK'."
Thanks and regards.
Ashwin v p
Ashwin VP
CSC India ...
August 4, 2009 at 11:51 pm
Ravi,
try
BACKUP DATABASE QAS
TO DISK ='Q:\SQLBackups\QAS.BAK'
Tanx 😀
August 4, 2009 at 11:53 pm
Good catch Ashwin 😉
Tanx 😀
August 5, 2009 at 2:41 am
Ashwin,
the default backup directory for my SQL instance is Q:\SQLBackups.
If you look at the error, it says that the path 'Q:\SQLBackups\Q:\SQLBackups\QAS\QAS.BAK'. is incorrect...
So, it looks like SQL Server is appending the path ('Q:\SQLBackups\QAS\QAS.BAK')that i give in the command to the default backup path(Q:\SQLBackups) and then trying to perform a backup.
the command that i am running is:
BACKUP DATABASE QAS
TO DISK ='Q:\SQLBackups\QAS\QAS.BAK'
this fails.
However, the below is working:
BACKUP DATABASE QAS
TO DISK ='QAS\QAS.BAK'
Isn't that strange??
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply