Backup Across Network?? 1

[jacktripper]

I've read instructions on how to do this, but apparently I am doing something wrong. Out of space on main SQL and want to set-up nightly backups to different network server.

At first, I tried mapping the other server & backup directory as a new drive N:\ on the SQL Server box. This failed.

I also tried using the network name of the server and directory, in this case "\\nas\backups\accounting.bak" and this also failed (cannot find path).

I can use IE to browse to both the N: drive and the \\nas\backups folder... but it seems that SQL Server just doesn't like them.

Thoughts? Not sure what I am doing wrong here. Permissions on the backup folder have been set to allow all for everyone.

 

[JTechMan]

I had something similar to this happen and found that you seem to need equivalent space free on the drive for the backup to occur, so, if your DB is 2GB then you have to have at least 2GB of free HDD space on the server drive, even if you are "technically" backing up to another machine. HTH.

 

[jacktripper]

Hmmm. But wouldn't I get a "not enough disk space" error in that case?

Well, I tried your suggestion and freed up enough space. I now have 5.5 Gig available on the SQL box, and my backup is about 3.9 Gig, so there is enough room. I still want to send it to the other server, however.

But, when I try to run the backup, I still get the error message saying "cannot find path" when I use the network name or the mapped N: drive.

Any other ideas?

 

[JTechMan]

It is possible that if SQL is running under a system account then it may not have network access but I am not sure.

 

[jacktripper]

Good point. That service is running as a local logon. Unfortunately, I can't stop the service and restart it with a network logon until after-hours... But, I will give it a shot.

Thanks.....

 

[JTechMan]

Any luck?

 

[mrdenny]

If you are backing up to a network share you don't need any local space for the backup. Unless you are using a third party tool to handle the backup, and that tool requires local space.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[jacktripper]

Yup!

Stopping the service and starting it with Network logon instead of local logon solved the issue. Thanks for the tip.

don't know what it was ever set to local anyway...

 

[jsh20fall]

I am having a similar problem...trying to manage a MSDE database using Sql Server 2005 management studio. I connected to the msde server from sql 2005 management studio and created the backup job. It fails to backup over the network. I believe my problem is permissions related --- is the backup job using the service account of the 2005 server or the msde server??

 

[jacktripper]

My service actually uses my login/pw which has full admin rights. The folder on the network has all permissions allowed, just to be sure.

What kind of error message are you getting?

What I had to do was go into the SQL machine admin services, stop the service, change the logon, and restart it again.

 

[jsh20fall]

The sql2005 server service has admin rights..and backs up several of it's own databases across the network with no problem. It is just this new database - on a different server that I am trying to manage from the 2005 server that I can't get to back up.

Everything I've read said it is permissions on the folder...I don't know what service account is running the msde database...so I'm not sure about those permissions.

Here's the error:
Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device '\\server\folder\backup\folder\folder\database.bkp'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201)

 

[jacktripper]

And you changed the permissions to allow the network service access?

 

[jacktripper]

I mean, if it were me, I'd just "allow everyone" and see if it works then.