Hi I am having problems backing up my transaction log.

The destination for the backup is not on the actual
server that the log is on, but another server. y:\ is a
mapped drive on my sql server.

Is sql capable of backing up to a network drive?

The message below is what i obtained by looking at the
server logs.

can someone help me please, if this goes another day
without a backup, i might halt my server as the log drive
will run out of space.

can i do a backup of the transaction log at any time?
does it cause disruption to users when it does happen?

regards,

Andrew

BACKUP failed to complete the command BACKUP LOG
[QUATTRO] TO DISK = N'y:\QUATTRO_tlog_200406110000.TRN'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Perhaps it's a rights issue. Try the following in Query Analyzer:

master..xp_cmdshell 'dir y:\'

If this fails, this means SQL Server does not have adequate rights to access
the network drive. You might need to change the startup account for the SQL
Server service then.

Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup


"Andrew Marks" <andrewmarks (AT) flowerspartners (DOT) com.au> wrote

Hi Peter, this is the result of your command in query
analyser.

I assume that this means that the backup job wont work?


Volume in drive Y is Downloads
Volume Serial Number is BC5F-74DB
NULL
Directory of y:\
NULL
File Not Found
NULL

I didn't actually see the y:\ in the folder options, I
mapped the drive then just put y:\in the command line.

what can a do to enable the backup to work?

regards,

Andrew

ps thanks for your reply.

You seem to be able to access the directory just fine. Do you have rights
to write to it using SQL Server credentials? Try copying a file over via QA
e.g.

master..xp_cmdshell 'xcopy c:\<some file on your local drive> y:\'

Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup


"Andrew MArks" <andrewmarks (AT) flowerspartners (DOT) com.au> wrote

Hi peter,
thanks for you reply. I tried your command and got the
following result.

E:\backup\Splitter.exe
Access denied
NULL

How do I get my sql server to access my file server
through the sql?

regards,

Andrew

Hi,

This means your SQL Server start up account do not have necessory previlage
to File server directory.
To do this you have to start your sql server using domain user which got
access to file server directory.

There are Few Pre requisites to do backup remotely;

1. You Should start SQL server service using Domain user who got access to
remote
machine Share

How to change the startup account:-
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.There you give a Valid Domain OS
user and password
to start the service. Now stop and start the MSSQL Server service

2. SQL server startup Domain user must have write access to the share in the
remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server
was started
4. Restart the services

Now you can execute the Backup script with UNC path

BACKUP Log <dbname> to disk='\\computername\sharename\dbname.bak' with
init

Note:

Backup to remote machine will not work if you start SQL server using Local
system account



Thanks
Hari
MCDBA





--
Thanks
Hari
MCDBA
"andrew marks" <andrewmarks (AT) flowerspartners (DOT) com.au> wrote

Perhaps your file server has not granted 'Write access' rights on this
folder.

Bear in mind that the SQL Server service starts up using the credentials of
either the Local system account or a named user. You can see this by
opening the Services panel on your server, open the properties page for the
MSSQLSERVER service, and select the Log on tab.

If you are currently using the Local System account and need to stick with
this, then I don't know what sort of rights you need to grant, and to which
named account, in order for the remote machine to explicitly allow this
account write access.

Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup


"andrew marks" <andrewmarks (AT) flowerspartners (DOT) com.au> wrote