Sql Backing up Tables: share

Showing posts with label share. Show all posts

Tuesday, March 20, 2012

Backup Device Location

Is it possible to point a backup device to a network location? I have a mapp
ed drive on my SQL Server to a share on another server (Q: drive). The backu
p device points to that Q drive. However, everytime I run the backup, it say
s it cannot reach the speci
fied location. The drive is mapped and the administrator is logged onto the
server, so there should not be a rights issue.
-BrianBrian,
You are better to use UNC names \\server\share\etc instead of mapped =
drive letters. Mapped drives are local to the account that mapped them, =
and therefore may not be visible to the account that the SQl Server =
service is running under. You need to check what account is being used =
for the MSSQLSERVER service and ensure that account has the necessary =
access rights to the remote server. Who is logged on at the other =
machine will make no difference.
Mike John
"Brian" <anonymous@.discussions.microsoft.com> wrote in message =
news:69161F98-4357-4CD0-900E-A229A9917C95@.microsoft.com...
> Is it possible to point a backup device to a network location? I have =
a mapped drive on my SQL Server to a share on another server (Q: drive). =
The backup device points to that Q drive. However, everytime I run the =
backup, it says it cannot reach the specified location. The drive is =
mapped and the administrator is logged onto the server, so there should =
not be a rights issue.
>=20
> -Brian|||Try using a UNC name instead of a mapped drive letter ie
backup database master to disk = '\\server2\backupshare\master.mak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:69161F98-4357-4CD0-900E-A229A9917C95@.microsoft.com...
> Is it possible to point a backup device to a network location? I have a
mapped drive on my SQL Server to a share on another server (Q: drive). The
backup device points to that Q drive. However, everytime I run the backup,
it says it cannot reach the specified location. The drive is mapped and the
administrator is logged onto the server, so there should not be a rights
issue.
> -Brian|||The UNC path worked. Thanks.sql

Backup device

Hi all,
I tried to create the new backup device with UNC path on the remote server
\\remoteserver\share on the sql server. But, I got "can not verify the
path" message and I have no problem to access the share through the sql
server. I checked the share and file security on the share and file and they
are fine.
I can not verify why I can not create UNC backup device on this sql server.
(windows 2003 R2 SP2 and sql 2000)
Can anyone help?
Try using sp_addumpdevice directly. It might just be the GUI which try to "assist" you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.discussions.microsoft.com> wrote in message
news:75486005-3FAB-499B-9C38-7C128149BFF9@.microsoft.com...
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?
|||> I have no problem to access the share through the sql
> server.
How did you access the share through the SQL instance?
Linchi
"John" wrote:

> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?

Backup device

Hi all,
I tried to create the new backup device with UNC path on the remote server
\\remoteserver\share on the sql server. But, I got "can not verify the
path" message and I have no problem to access the share through the sql
server. I checked the share and file security on the share and file and they
are fine.
I can not verify why I can not create UNC backup device on this sql server.
(windows 2003 R2 SP2 and sql 2000)
Can anyone help?Try using sp_addumpdevice directly. It might just be the GUI which try to "assist" you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John" <John@.discussions.microsoft.com> wrote in message
news:75486005-3FAB-499B-9C38-7C128149BFF9@.microsoft.com...
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?|||> I have no problem to access the share through the sql
> server.
How did you access the share through the SQL instance?
Linchi
"John" wrote:
> Hi all,
> I tried to create the new backup device with UNC path on the remote server
> \\remoteserver\share on the sql server. But, I got "can not verify the
> path" message and I have no problem to access the share through the sql
> server. I checked the share and file security on the share and file and they
> are fine.
> I can not verify why I can not create UNC backup device on this sql server.
> (windows 2003 R2 SP2 and sql 2000)
> Can anyone help?sql

Friday, February 10, 2012

backing up to network share issue.

Hi,
As mentioned in a previous post, I am trying to backup to a network drive. When I add the dump device using the unc and try to backup, I get the following error message "cannot open backup device. Device error or device off-line.." So I did research on
this error and found out a MS kb which states that this might be happening if I do not have write access to that drive. Now I do have write access there but I know it has something to do with SQL Server trying to write to that shared drive in disguise of
local account instead of me.. (as account). Now, another article here http://www.winnetmag.com/Article/Art...025/14025.html describes what needs to be done but the problem is that I can do all this at my office. But we are creating shrink wrapped
app using MSDE and we will be providing a simple user interface to the client to do backup/restore. So what should I tell my clients or even best what should I do so that the backups to the network share work seemlessly.
The quest is unsatiable!!
This is really a windows issue, not a SQL Server issue. Every piece of software runs under a user account in
Windows (NT). And every user who accesses a resource (like a share) need permissions to that resource. If the
software (user) - SQL Server in this case - doesn't have access to the share, then Windows will not allow
access. You have to make sure that SQL Server is using an account that has permissions on the share in
question.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"whynot" <anonymous@.discussions.microsoft.com> wrote in message
news:92D21681-63A8-40DD-8C86-583DA99F27FB@.microsoft.com...
> Hi,
> As mentioned in a previous post, I am trying to backup to a network drive. When I add the dump device using
the unc and try to backup, I get the following error message "cannot open backup device. Device error or
device off-line.." So I did research on this error and found out a MS kb which states that this might be
happening if I do not have write access to that drive. Now I do have write access there but I know it has
something to do with SQL Server trying to write to that shared drive in disguise of local account instead of
me.. (as account). Now, another article here http://www.winnetmag.com/Article/Art...025/14025.html
describes what needs to be done but the problem is that I can do all this at my office. But we are creating
shrink wrapped app using MSDE and we will be providing a simple user interface to the client to do
backup/restore. So what should I tell my clients or even best what should I do so that the backups to the
network share work seemlessly.
> The quest is unsatiable!!

backing up to a share from an sql 2000 cluster

Hi there,
Does any know a way of backing up to a share from an sql
2000 active/active cluster. I have no problems doing this
for stand alone installtions of sql 2000, but just can't
seem to get it to work from a cluster.
any help appreciated
Shaun
This shouldn't be an issue. What exactly is the problem you are having?
Can't get it to work isn't much of a clue<g>.
Andrew J. Kelly SQL MVP
"Shaun Buckley" <anonymous@.discussions.microsoft.com> wrote in message
news:4baf01c4805a$1f9113f0$a501280a@.phx.gbl...
> Hi there,
> Does any know a way of backing up to a share from an sql
> 2000 active/active cluster. I have no problems doing this
> for stand alone installtions of sql 2000, but just can't
> seem to get it to work from a cluster.
> any help appreciated
> Shaun
|||http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shaun Buckley" <anonymous@.discussions.microsoft.com> wrote in message
news:4baf01c4805a$1f9113f0$a501280a@.phx.gbl...
> Hi there,
> Does any know a way of backing up to a share from an sql
> 2000 active/active cluster. I have no problems doing this
> for stand alone installtions of sql 2000, but just can't
> seem to get it to work from a cluster.
> any help appreciated
> Shaun

Backing up to a network share from SQL Server 2005

Hello-
I'm trying to back my database up to a network share. Using the same account
that I'm logged into SQL Server Management Studio with, I'm able to add and
delete files on the network share just fine. When I try to do a backup to the
same location, I get this message:
System.Data.SqlClient.SqlError: Cannot open backup device
'\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
denied.). (Microsoft.SqlServer.Smo)
Does anyone know why this might be or how best to troubleshoot it?
Regards-
EricIt is the SQL Server service account that need permissions on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> Hello-
> I'm trying to back my database up to a network share. Using the same account
> that I'm logged into SQL Server Management Studio with, I'm able to add and
> delete files on the network share just fine. When I try to do a backup to the
> same location, I get this message:
> System.Data.SqlClient.SqlError: Cannot open backup device
> '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> denied.). (Microsoft.SqlServer.Smo)
> Does anyone know why this might be or how best to troubleshoot it?
> Regards-
> Eric|||Tibor-
Thank you. Exactly the info I was looking for. One more question. I have the
SQL Server Agent running under the local system account. Do you know if it is
possible to give the local system account permissions on a network share? If
not, I can either backup to a local file and then copy it onto the network
share, or I can change the service the agent runs under. Any recommendations
or best practices?
Regards-
Eric
"Tibor Karaszi" wrote:
> It is the SQL Server service account that need permissions on the share.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> > Hello-
> >
> > I'm trying to back my database up to a network share. Using the same account
> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> > delete files on the network share just fine. When I try to do a backup to the
> > same location, I get this message:
> >
> > System.Data.SqlClient.SqlError: Cannot open backup device
> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> > denied.). (Microsoft.SqlServer.Smo)
> >
> > Does anyone know why this might be or how best to troubleshoot it?
> >
> > Regards-
> > Eric
>
>|||It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
you probably want to talk to some Windows experts on this. I think you can elaborate with the
machine account and get LocalSystem to work, but I've never done it. I always use a domain account
when I need for my SQL Servers to reach outside the box.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> Tibor-
> Thank you. Exactly the info I was looking for. One more question. I have the
> SQL Server Agent running under the local system account. Do you know if it is
> possible to give the local system account permissions on a network share? If
> not, I can either backup to a local file and then copy it onto the network
> share, or I can change the service the agent runs under. Any recommendations
> or best practices?
> Regards-
> Eric
> "Tibor Karaszi" wrote:
>> It is the SQL Server service account that need permissions on the share.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
>> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
>> > Hello-
>> >
>> > I'm trying to back my database up to a network share. Using the same account
>> > that I'm logged into SQL Server Management Studio with, I'm able to add and
>> > delete files on the network share just fine. When I try to do a backup to the
>> > same location, I get this message:
>> >
>> > System.Data.SqlClient.SqlError: Cannot open backup device
>> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
>> > denied.). (Microsoft.SqlServer.Smo)
>> >
>> > Does anyone know why this might be or how best to troubleshoot it?
>> >
>> > Regards-
>> > Eric
>>|||Tibor-
Are you sure there isn't a SQL Server setting that might be preventing me
from backing up to a network share? I set up a domain account for the Sql
Server Agent to run under and it still fails. I set permissions on the
network share to give Everyone full control. I also tried running the TSQL
for the backup via a query (logged in as a domain admin) and I still get the
same access denied error.
Regards-
Eric
"Tibor Karaszi" wrote:
> It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> you probably want to talk to some Windows experts on this. I think you can elaborate with the
> machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> when I need for my SQL Servers to reach outside the box.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> > Tibor-
> >
> > Thank you. Exactly the info I was looking for. One more question. I have the
> > SQL Server Agent running under the local system account. Do you know if it is
> > possible to give the local system account permissions on a network share? If
> > not, I can either backup to a local file and then copy it onto the network
> > share, or I can change the service the agent runs under. Any recommendations
> > or best practices?
> >
> > Regards-
> > Eric
> >
> > "Tibor Karaszi" wrote:
> >
> >> It is the SQL Server service account that need permissions on the share.
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> >> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> >> > Hello-
> >> >
> >> > I'm trying to back my database up to a network share. Using the same account
> >> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> >> > delete files on the network share just fine. When I try to do a backup to the
> >> > same location, I get this message:
> >> >
> >> > System.Data.SqlClient.SqlError: Cannot open backup device
> >> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> >> > denied.). (Microsoft.SqlServer.Smo)
> >> >
> >> > Does anyone know why this might be or how best to troubleshoot it?
> >> >
> >> > Regards-
> >> > Eric
> >>
> >>
> >>
>
>|||I think I've got this. It's a strage file share permission problem. I"ll move
this over to a Windows Server discussion group. Thanks for your help.
Regards-
Eric
"Eric Marthinsen" wrote:
> Tibor-
> Are you sure there isn't a SQL Server setting that might be preventing me
> from backing up to a network share? I set up a domain account for the Sql
> Server Agent to run under and it still fails. I set permissions on the
> network share to give Everyone full control. I also tried running the TSQL
> for the backup via a query (logged in as a domain admin) and I still get the
> same access denied error.
> Regards-
> Eric
> "Tibor Karaszi" wrote:
> > It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> > you probably want to talk to some Windows experts on this. I think you can elaborate with the
> > machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> > when I need for my SQL Servers to reach outside the box.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> > news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> > > Tibor-
> > >
> > > Thank you. Exactly the info I was looking for. One more question. I have the
> > > SQL Server Agent running under the local system account. Do you know if it is
> > > possible to give the local system account permissions on a network share? If
> > > not, I can either backup to a local file and then copy it onto the network
> > > share, or I can change the service the agent runs under. Any recommendations
> > > or best practices?
> > >
> > > Regards-
> > > Eric
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> It is the SQL Server service account that need permissions on the share.
> > >>
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> > >> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> > >> > Hello-
> > >> >
> > >> > I'm trying to back my database up to a network share. Using the same account
> > >> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> > >> > delete files on the network share just fine. When I try to do a backup to the
> > >> > same location, I get this message:
> > >> >
> > >> > System.Data.SqlClient.SqlError: Cannot open backup device
> > >> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> > >> > denied.). (Microsoft.SqlServer.Smo)
> > >> >
> > >> > Does anyone know why this might be or how best to troubleshoot it?
> > >> >
> > >> > Regards-
> > >> > Eric
> > >>
> > >>
> > >>
> >
> >
> >|||On Tue, 28 Nov 2006 16:10:02 -0800, Eric Marthinsen
<EricMarthinsen@.discussions.microsoft.com> wrote:
>Tibor-
>Are you sure there isn't a SQL Server setting that might be preventing me
>from backing up to a network share? I set up a domain account for the Sql
>Server Agent to run under and it still fails. I set permissions on the
>network share to give Everyone full control. I also tried running the TSQL
>for the backup via a query (logged in as a domain admin) and I still get the
>same access denied error.
>Regards-
> Eric
The Agent Service is not performing the backup, it is directing SQL
Server to back itself up. As was said in previous messages, the
account that needs the rights is the account the SQL Server service is
running under.
When I was up to date on this stuff a few years ago the only way to
allow the local system account access to a network resource amounted
to removing all restrictions and security on the target system. I
doubt it is any safer today, in which case you should look into
creating a proper account for the SQL Server service.
Roy Harvey
Beacon Falls, CT

Tuesday, March 20, 2012

Backup Device Location

Backup device

Backup device

Friday, February 10, 2012

backing up to network share issue.

backing up to a share from an sql 2000 cluster

Backing up to a network share from SQL Server 2005

Backing up to a network share from SQL Server 2005

Backing up to a network share from SQL Server 2005

Sql Backing up Tables

Blog Archive

About Me