Backing up SQL across the network

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Backing up SQL across the network

Author

Topic

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-08 : 18:16:20

I was doing some testing, because I want to start looking into ways to start backing up our SQL server to a fileserver I have up on our network with LOTS of room.

Poking around on the net and in here, I see you have to use UNC methods.

Here is what I came up with:

BACKUP DATABASE Northwind 
TO DISK = '\\cmfs01\data\dbtest\Northwind.bak'

However, I get a error:


Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\cmfs01\data\DBTest\Northwind.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I made sure the the "share", 'data', is FULLY open. I have everyone with FULL control.

Is it something I am missing on the code?

Bugging me.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-08 : 18:39:15

If your SQL Server is running under the local system account then I think even Everyone on the fileserver won't work. You need to use a network user account, either a domain user if the servers are both in a domain, or just create an account with the same name and password on both servers and run the SQL Server service under that.

See the following for info regarding requirements for the service account
2000 - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
2005 - http://msdn2.microsoft.com/en-us/library/ms143504.aspx

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-08 : 18:54:47

quote:
Originally posted by snSQL

If your SQL Server is running under the local system account then I think even Everyone on the fileserver won't work. You need to use a network user account, either a domain user if the servers are both in a domain, or just create an account with the same name and password on both servers and run the SQL Server service under that.

See the following for info regarding requirements for the service account
2000 - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
2005 - http://msdn2.microsoft.com/en-us/library/ms143504.aspx

I was thinking it might be a permission issue of some sort, but was not sure. That is very helpful.

Ironicaly, the fileserver is in a Domain and this particular sql box is not (in the workgroup, but not the domain...long story really)

Also, I am pretty sure that is running under a local system account. I checked the services and under the "log on" tab, it has "local system account."

Which brings me to a question about this account; what account is it running under? I did not set this server up, I inherited it. Is there a way to find out the username and password, so I can create it on the other server?

Thanks for the links. They are very helpful.

EDIT: By the way, the first link you provided, I can get to the link, but when I try to expand some of the suggestions (like how to change the password in EM), it doesn't work.

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-08 : 19:29:10

quote:
Originally posted by snSQL
or just create an account with the same name and password on both servers and run the SQL Server service under that.

Very newbie question here:
If I create a account, on both boxes (say dbadmin) and tell SQL to use that account to run under, does that have any effect on the database at all? I mean, we have a test environment that this box uses...does it effect that at all?

Just want to know if anything is changed that could cause problems if I tell SQL to run under a different account.

Thanks,

Kristen
Test

22859 Posts

Posted - 2006-12-09 : 02:11:27

General advice: Don't backup across the network. Backup locally and then COPY / MOVE the file once the backup is complete. This insulates you from network problems - otherwise a network problem means you have no backup!

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-12-09 : 07:45:18

The SQL needs to be run in domain\sql and the sql agent also not local

Then in the backups you can say
\\backup directory.

We have all our backups going across the network to a SAN then the veritas backup takes these files and put to tape.

If the SQL is local it will not work .

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-09 : 13:24:37

quote:
Originally posted by TRACEYSQL

The SQL needs to be run in domain\sql and the sql agent also not local

Then in the backups you can say
\\backup directory.

We have all our backups going across the network to a SAN then the veritas backup takes these files and put to tape.

If the SQL is local it will not work .

Ya, that is what I want to do. I am going to be purchasing a SAN device here pretty soon. In the meantime, I am trying to lock down the correct method to get this to be able to work.

If I created an account on the local box and say a file server box (same name and password), and told SQL to run under that account, would that work? If by changing the name SQL runs under, does that cause any problems for the SQL server box itself?

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-10 : 01:39:50

quote:
If I created an account on the local box and say a file server box (same name and password), and told SQL to run under that account, would that work? If by changing the name SQL runs under, does that cause any problems for the SQL server box itself?

Not as long as you go over the requirements for the service account in the links I referred to before.

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-11 : 13:38:58

quote:
Originally posted by snSQL

quote:
If I created an account on the local box and say a file server box (same name and password), and told SQL to run under that account, would that work? If by changing the name SQL runs under, does that cause any problems for the SQL server box itself?

Not as long as you go over the requirements for the service account in the links I referred to before.

Reading those links you provided, it specifically talks about the local service account and a domain account. Now, right now, this box is not part of the domain (yet, in the future though), but from what I read, as long as I create an account for the SQL server and Agent to run under that is part of the administrators group, everythign should work properly, correct?

Thanks,

TCG

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-12 : 05:00:43

Hi CoffeeGuy (cool name, I could do with a good cup of Blue Mountain right now...)

Thinking back, I looked into this same thing earlier this year, I agree with Kristen, for whatever reason like excess overhead, or ineffecient packet-handling etc, SQL Server simply isn't competent at cross-network backups. Honestly, save yourself the heartache. It's just too dicey, and you don't want to be left saying, "Ermmmm, I thought as it's a Microsoft RDBMS and a Microsoft domain and we don't have any network bottlenecks, everything would just be ok..."

The good news is that there are some handy tools out there that transfer .bak files with near 100% success, and that won't make the beancounters faint. Better yet, there is XCopy, which I think comes bundled with NT, I used it for months without it failing once - come to think of it, that's what I ended up using, I just got so used to the .bak files being on the SAN I forgot how they even got there!!!!

Cheers,

JB

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 06:30:23

IMHO RoboCopy would probably be a better choice than XCopy (RoboCopy comes with the Resource Kit [i.e. also "free"] AFAIK)

Kristen

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-12-12 : 08:28:03

quote:
Originally posted by Kristen

IMHO RoboCopy would probably be a better choice than XCopy (RoboCopy comes with the Resource Kit [i.e. also "free"] AFAIK)

Kristen

Actually, they're both free, though apart from performing the same basic function, each has it's own distinct list of useless add-ons...the great thing for all of us is that the decision gets othersourced to the Network Guy, (I mean...they've got to be good for SOMETHING...:) ) so whichever he is the most familiar with is the one that gets implemented. There's one called XXCopy that is 3rd party, I'm sure there are others.

If I were CoffeeGuy I'd simply say SQL isn't good at cross-network backups for the reasons stated above, and throw it over to the N/A.

JB

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-12 : 14:14:02

quote:
Originally posted by Jaybee from his castle

Hi CoffeeGuy (cool name, I could do with a good cup of Blue Mountain right now...)

Thinking back, I looked into this same thing earlier this year, I agree with Kristen, for whatever reason like excess overhead, or ineffecient packet-handling etc, SQL Server simply isn't competent at cross-network backups. Honestly, save yourself the heartache. It's just too dicey, and you don't want to be left saying, "Ermmmm, I thought as it's a Microsoft RDBMS and a Microsoft domain and we don't have any network bottlenecks, everything would just be ok..."

The good news is that there are some handy tools out there that transfer .bak files with near 100% success, and that won't make the beancounters faint. Better yet, there is XCopy, which I think comes bundled with NT, I used it for months without it failing once - come to think of it, that's what I ended up using, I just got so used to the .bak files being on the SAN I forgot how they even got there!!!!

Cheers,

JB

One of the problems I am current having is on one of our test SQL servers. I have about 18gigs free on the drive, but the one DB on that server we use, has a DB size of about 18gigs and TLOG size of our 26gigs.

I figure, I can't backup locally because I will break the machine. I can't add any drives to it because the RAID in use does not allow extra drives to be added.

That is why I was thinking about pushing to a fileserver, then maybe doing some work to free up space on the box.

Any suggestions there?

Thanks,

TCG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-12 : 14:30:48

quote:
Originally posted by Jaybee from his castle

If I were CoffeeGuy I'd simply say SQL isn't good at cross-network backups for the reasons stated above, and throw it over to the N/A.

This isn't true. I don't recommend performing backups to another server, but it isn't because SQL Server can't handle it. The issue has to do with the network. I always recommend to backup locally then copy the file to wherever it needs to go. We copy our files to tape plus over to our disaster recovery site.

If someone wants to recap what the problem is rather than me having to read this lengthy thread, then I'd be happy to help.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-12 : 14:34:43

quote:
Originally posted by tkizer

If someone wants to recap what the problem is rather than me having to read this lengthy thread, then I'd be happy to help.

Tara Kizer

Sure...

I have a test SQL DB box that we use for our systems (Siebel if anyone is familiar.) I only have 18gigs free on it, but my DB size is about 20gigs and the TLOGS are 26gigs (shooting from hip here.)

All I know is that if I backup locally, it will fill the disk.
So, I could try going across the network to a fileserver for both. But after that, is there anyway to "free" up some space?

One more question for you Tara

Your blog site is great and the scripts are very helpful. But...

1.) Sounds silly, but how do I execute those scripts?

2.) little off topic, but how can I get better and learning to generate SQL queries?

THanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-12 : 14:58:27

What issue are you having backing up the database to another server? Could you post the error?

1. You can execute them directly in Query Analyzer but if you want to use them on a regular basis, then you set them up in jobs.

2. Books, books, books, and maybe training. I don't recommend training unless you are a complete newbie. I went to a T-SQL training course once and it was way too beginner for me.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-12 : 15:31:23

quote:
Originally posted by tkizer

What issue are you having backing up the database to another server? Could you post the error?

Actually, I was able to get it working. I created a specific account for SQL and the SQLAgent to run under. I then created that account on the UNIX file server with permission to write to it. I just tested it and it worked.

quote:

1. You can execute them directly in Query Analyzer but if you want to use them on a regular basis, then you set them up in jobs.

I would just need to change the parameters in the script itself?

quote:

2. Books, books, books, and maybe training. I don't recommend training unless you are a complete newbie. I went to a T-SQL training course once and it was way too beginner for me.

Ordered the Guru's guide to T-SQL I believe it is called?
I 'll into more.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-12 : 15:41:57

When you go to execute my stored procedures, that's where you tell it what values to pass them. Check out the example portion in the comment header block of my scripts.

All of the guru books are great. They might be a little too advanced though if you are a newbie.

Tara Kizer

thecoffeeguy
Yak Posting Veteran

98 Posts

Posted - 2006-12-12 : 16:00:14