Server backup to network drive failure
Jeffery Partridge
backup-directory The target location on disk for the backup files, relative
to the database server's current directory at startup.
Why does this sometimes fail for network drives with an error message cannot
create the folder, even when it is already there?
Full permission appear to be available for the folder.
Frank Ploessel
Are you running your DB as a service? In this case, if it is running under
a user ID of "local system", it does not have any access to the network.
You could run the service under another user id thtat has rights in the
network.
Frank
Jeffery Partridge
not have priviledges to write to a network drive.
What else could prevent the database from writing to a network drive? The
drive has a drive letter and everything, and Guest acccounts are even turned
on at the the system that the network drive is physically on.
"Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote in message
news:op.t5z2y...@bonw00164.internal.imsglobal.com...
Jeff Albion (Sybase iAnywhere)
> I've tried all users and even created new ones to no avail. It still does
> not have priviledges to write to a network drive.
> What else could prevent the database from writing to a network drive? The
> drive has a drive letter and everything, and Guest acccounts are even turned
> on at the the system that the network drive is physically on.
Jeff,
Things I'm wondering:
- What version is this?
- How is the database server being run? Is it as a service? If you
create a user (i.e. "Windows User"), have you been changing the database
service information and restarting?
- How are you backing up? By your first message, I'm assuming this is
the BACKUP DATABASE statement, but could we see it? Is this getting
executed in an event, stored procedure, ...? Could we see the SQL?
- What happens with:
CALL xp_write_file('<mappeddrive>:\\test.txt', 'Hello!');
- Does the dbbackup command have any success writing to that drive?
- What is the 'network drive' your referring to? A hosted Linux Samba
share, a Windows Share, a NAS device, ...?
Regards,
--
Jeff Albion, Product Support Analyst
Sybase iAnywhere
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Frank Ploessel
If you are talking of users, are you referring to database user ids or to
Windows user ids and change the db service configuration to run under that
user id?
I was talking of the latter, but it sounds to me that you did the first.
Frank
On Wed, 06 Feb 2008 16:31:21 +0100, Jeffery Partridge
Jeffery Partridge
> - What version is this?
> - How is the database server being run? Is it as a service? If you create
> a user (i.e. "Windows User"), have you been changing the database service
> information and restarting?
The database server is being run as a Windows Service, starting
automatically with Windows. The service was stopped and restart for each
user change.
> - How are you backing up? By your first message, I'm assuming this is the
> BACKUP DATABASE statement, but could we see it? Is this getting executed
> in an event, stored procedure, ...? Could we see the SQL?
Here is the backup stored procedure we are calling through an ADO
connection:
ALTER PROCEDURE "SMP"."sp_backup_now"()
BEGIN
DECLARE cmd LONG VARCHAR;
DECLARE bfolder LONG VARCHAR;
DECLARE btype SMALLINT;
DECLARE day_name CHAR(20);
DECLARE dbpath LONG VARCHAR;
DECLARE iIndex INTEGER;
// get backup folder from the SpecialInfo table
SELECT backupfolder INTO bfolder FROM "SMP"."SpecialInfo" WHERE Idx = 1;
// if the table hasn't been setup, use default install location
IF bfolder IS NULL THEN
// JSP 07/11/07 set bfolder = 'c:\program files\mmipos\backup';
SELECT db_property('FILE') INTO dbpath;
SET iIndex = LOCATE(dbpath,'\\',-1);
//find the slash before database name (-1 does instrrev)
SELECT SUBSTR(dbpath,1,iIndex-1) INTO bfolder;
//move the path into bfolder
SET iIndex = LOCATE(bfolder,'\\',-1);
//find the slash before D_MDB folder
SET bfolder = SUBSTR(bfolder,1,iIndex) || 'backup';
//strip off the DATA folder and add 'backup'
END IF;
// if no record, do not use seperate folders for each day
SELECT seperatedays INTO btype FROM "SMP"."SpecialInfo" WHERE Idx = 1;
IF btype = 1 THEN
// get todays day name for folder use
SET day_name = DATENAME(WEEKDAY, CURRENT DATE);
// append the day's name
SET bfolder = bfolder || '\\' || day_name;
END IF;
// run incremental backup
// JSP 07/12/07 BACKUP DATABASE DIRECTORY bfolder WAIT BEFORE START
TRANSACTION LOG RENAME;
BACKUP DATABASE DIRECTORY bfolder WAIT BEFORE START TRANSACTION LOG
TRUNCATE;
// update the date field with the backup time
SET cmd = 'UPDATE SpecialInfo Set LastBackup = GetDate() Where Idx = 1';
EXECUTE IMMEDIATE WITH ESCAPES OFF cmd;
// update the location of this backup
SET cmd = 'UPDATE SpecialInfo Set LastBackupLocation = ''' || bfolder ||
''' Where Idx = 1';
EXECUTE IMMEDIATE WITH ESCAPES OFF cmd;
END
> - What is the 'network drive' your referring to? A hosted Linux Samba
> share, a Windows Share, a NAS device, ...?
The folder is U:\Temp on a Windows Server2003 and the message is:
Error during backup, could not create directory "u:\temp" - No such file
or directory
> - What happens with:
> CALL xp_write_file('<mappeddrive>:\\test.txt', 'Hello!');
This does not report an error, but it does not write a file either. I can
echo the same thing from a DOS prompt and it creates the file without a
problem.
> - Does the dbbackup command have any success writing to that drive?
dbbackup backs up the database just fine to the same location.
I tried BACKUP DATABASE DIRECTORY 'u:\temp' WAIT BEFORE START TRANSACTION
LOG TRUNCATE; in dbISQL and it results with the same error.
I tried BACKUP DATABASE DIRECTORY 'u:\\temp' WAIT BEFORE START TRANSACTION
LOG TRUNCATE; in dbISQL and it results with the same error.
I hope this helps you to understand our dilema. We are getting this problem
reported to us be every one of our customers that we've deployed to that try
to use a network drive as the backup location. This seems like common sense
to create an off-system backup for security reasons in case the server
crashes.
Thank you for looking into this situation. We're getting screamed at here.
Regards,
Jeff
"Jeff Albion (Sybase iAnywhere)" <firstname...@ianywhere.com> wrote in
message news:47a9eea3@forums-1-dub...
Jeff Albion (Sybase iAnywhere)
> The database server is being run as a Windows Service, starting
> automatically with Windows. The service was stopped and restart for each
> user change.
Is this server on a domain? Are all of the "users" (that you're creating
to run the service) domain users that have r/w access to that drive?
> No such file or directory
(And all the of the domain users have that drive mapped at login?)
Try re-writing it with the UNC name to see if the domain users have
better luck:
BACKUP DATABASE DIRECTORY '\\\\myserver\\myshare\\mybackupdir' ...
>> - What happens with:
>> CALL xp_write_file('<mappeddrive>:\\test.txt', 'Hello!');
> This does not report an error, but it does not write a file either. I can
> echo the same thing from a DOS prompt and it creates the file without a
> problem.
Which tells me that it's related to permissions writing to the drive in
the context that the server is running.
>> - Does the dbbackup command have any success writing to that drive?
> dbbackup backs up the database just fine to the same location.
This also tells me that it's related to permissions writing to the drive
in the context that the server is running.
> Thank you for looking into this situation. We're getting screamed at here.
As always, if "getting screamed at" implies that the issue requires
immediate attention, we highly recommend you open a technical support
case. ( http://www.sybase.com/contactus/support#tech )
Jeffery Partridge
system (XP Pro) and tried the backup from dbISQL.
When I used the drive letter, it failed. When I used the UNC, it allowed
it.
I made sure I removed the mapping and remapped using the same user account
that I start the service with, but I still cannot backup to a remote drive
using the mapped letter. I cannot backup up at all if I start the service
as Local System Account. What is crazy is that my log into my system is
SOURCE SYSTEM, which is the Administrator account. Yet, I must specifically
run the service as SOURCE SYSTEM and use a UNC before I can do the backup.
We are not using domains at all here. However, we do have customers that
are with the same issues.
I understand that it would be a permissions issue as to how the service is
running, I just don't get why the same account creating the mapping for the
drive letter is having an issue creating the folder under the service.
As the server, I cannot backup at all to any local drive using the UNC.
Also, I cannot backup up at all to a remote drive unless I use a UNC (and
not use the Local System Account). Does this seem like normal behaviour
because of the service?
I just created a Maintainance Plan and it had the same results.
"Jeff Albion (Sybase iAnywhere)" <firstname...@ianywhere.com> wrote in
message news:47ab389a$1@forums-1-dub...
Jeff Albion (Sybase iAnywhere)
================================================================
*** Note: The proceeding is not a recommended solution for backing up a
database. Share mapping is fraught with pitfalls, particularly if
network connectivity is interrupted at any point. SANs, RAID
configurations, and other similar multi-drive technology is MUCH better
suited to this task if there is a concern for local disk failure.
Performing a database backup to a local resource, then using another
backup program to transfer it to an "off-site" backup is the recommended
technique.
================================================================
Here is my understanding of the prerequisites for getting the scenario
you have proposed working:
1. The service must be running under a user account that's valid on the
database server system, that's not the "Local System" account ("Local
System" does not have access to network resources, period).
2. The user that runs the service would have to have login permissions
set up on its account to log in to the remote network share (i.e. the
user "MYDBSERVER\svcrunner" has a login and password associated to the
remote server, such as the user "MYNFSSERVER\shareusr"). This would need
to be associated in the user control panel (Under "Manage my network
passwords").
3. The user has a persistent (!) mapping set up to the drive (i.e.
"Reconnect at login" is enabled or "/persistent:yes" is used on the "net
use" command).
I haven't tried the persistent mapping bit myself, but I'm assuming
that's what would be required if this has any hope of working.
If the above doesn't work - is using UNC paths a problem at this point?
---
> When I used the drive letter, it failed. When I used the UNC, it allowed it.
I'm assuming that means that either persistent mapping wasn't enabled,
or it just won't work since Windows will only remap the drive when the
user actually *logs in*, as opposed to whatever it does when it utilizes
the specific user to run a service.
> I cannot backup up at all if I start the service as Local System Account.
That is expected behaviour. See my first point.
Jeffery Partridge
done the following:
Our utility that sets up the backup location will store a mapped drive as a
UNC, a local drive as a drive letter. The service will be required to run
as a non Local Service Account if they want to backup to a remote drive.
This should take care of the backup issues.
Thanks again for everyone's help.
Jeffery Partridge
"Jeff Albion (Sybase iAnywhere)" <firstname...@ianywhere.com> wrote in
message news:47ab7f14@forums-1-dub...