Clustered SQL Server backup & local drives

Question

Clustered SQL Server backup & local drives

Mateus Espadoto

Mr or Mrs. 500

Points: 564
More actions
June 5, 2003 at 5:16 am

#105866

Sorry if I didin't get the point here, but
I disagree with the affirmation that the backup drive must be in the SQL cluster group to be visible by SQL Server.
I have a 2-node cluster on which I do the backups of the active node on a local drive, not shared, because of space limitations on
our storage array. Then we do full server backups on a daily basis to maintain all the dumps, in a case of a failure.
IMHO, this is a SQLEM limitation.
Thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65138 More actions · Answer 1

I have no experience or knowledge of clustered servers so I found the question impossible to answer. Took a guess and got it wrong

I looked on the web and found the following on another site

http://www.sql-server-performance.com/q&a79.asp

which seems to disprove the correct answer!!

Far away is close at hand in the images of elsewhere.
Anon.

Randy Rabin SSCommitted Points: 1511 More actions · Answer 2

Agreed with mespadoto. SQL can *backup* to any drive anywhere on the network, as long as it has proper permissions to a share on that drive. I thought this was a trick question (and got it wrong anyway <g>) turns out I guess I tried to read too much into it.

I want my money back .. oh wait, it's free.... 🙂

Brian Knight One Orange Chip Points: 27249 More actions · Answer 3

Just retested the answer again. Opened up EM for a SQL Server in a cluster, tried to backup to the C: drive and it bombed saying folder doesn't exist. Interesting thing was though that it did work in Query Analyzer. Pulled up a list of drives also to backup through EM and the C drive couldn't be seen there as well. The workaround would be to backup to the C drive via QA but you would never want to do that. Glad it's causing some debate!

Brian Knight

bknight@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bknight

Brian Knight
Free SQL Server Training Webinars

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi David,

quote:
I have no experience or knowledge of clustered servers so I found the question impossible to answer. Took a guess and got it wrong

I have also no experience on MSCS and haven't found it in BOL. From what I have read, I have explained it to myself this way:

There can be more than one server in a cluster, right?

So, when there can be more than one, who decides which C:\ drive to take?

So it sounds logical to me, to disallow Backups to C:\.

And I guessed right.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mateus Espadoto Mr or Mrs. 500 Points: 564 More actions · Answer 5

quote:

So, when there can be more than one, who decides which C:\ drive to take?

Frank,

SQL Server uses the C: drive of the active node on this case. It works that way for any drive not included in the SQL Server cluster group.

So the backup is *not* disallowed by the server on local drives. The point is that EM blocks this for some reason.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 6

You can backup database to the local drive at active node but the backup will be inaccesable once SQL Server fails over to another node. For best practice, I would backup the database to the share drivers.

Edited by - Allen_Cui on 06/05/2003 08:47:37 AM

David Burrows SSC Guru Points: 65138 More actions · Answer 7

Hi Frank,

Not to labour this point but I did read some articles on web regarding clustering which stated that each server had a C:\ drive where the software (ie SQL) was installed and that all the servers in the cluster must have the same software folder (e.g C:\Program Files\MSSQL7). This may be a difference between active/passive and active/active options, I don't know, I'm only a pleb where clusters are concerned.

I attached a url in my first post that seemed to dispute the statement that you cannot backup to C:, it stated that there could be a bug or feature of EM that does not show C: in it's list, but you can specify the full path insteads and it would work.

However since Brian has tried this and must know all about clustering, the info I found must be out of date or wrong.

Nice to know someone has done this as my boss keeps mentioning such things to me as if I am the fountain of all knowledge (which I ain't).

Oh! to be a guru and know all the answers eh!

Far away is close at hand in the images of elsewhere.
Anon.

Mateus Espadoto Mr or Mrs. 500 Points: 564 More actions · Answer 8

Exactly.

As I said, I use this approach due to a disk space limitation.

I don't think that this is the best

solution. Far from that...

I was just saying that it is possible.

Thanks

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hi mespadoto,

quote:
SQL Server uses the C: drive of the active node on this case. It works that way for any drive not included in the SQL Server cluster group.

thanks for explanation!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

locklin SSC-Addicted Points: 450 More actions · Answer 10

I agree with the argument that this is just a limitation of Enterprise Manager (albeit maybe intentionally to save people from themselves). SQL Server CAN in fact see local drives in a cluster. While I don't send my backups there, I do have other processes that interact with the C drive. It just happens to be the C drive of whichever machine happens to be running the cluster at the time. As others have pointed out, there are ways to send backups to the C drive in a cluster if you really want to. I think that the question/answer is a little misleading and could use some clarification.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 11

OK, this all said. The quizz question was regarding EM. So for most of us points not won. btw that's not equal to points lost

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

jbheather SSC-Addicted Points: 439 More actions · Answer 12

Maybe I'm dumb, but this thread just got me confused. So I tried a backup to c: on my cluster (2 nodes, SQL2K). It works just fine for me from EM. I did create the folder on c: first.

swhalen SSC Journeyman Points: 83 More actions · Answer 13

I agree with those that think this question is badly flawed. As others have noted, SQL Server can in fact "see" local drives, so the "correct" answer is "incorrect" ... None of the other answers is worth writing home about so I think this cluster question should be tossed, and a better one on clustering included later ...

Brian Knight One Orange Chip Points: 27249 More actions · Answer 14

Keep in mind that the question was on why can't Enterprise Manager see the local drives.

Brian Knight

bknight@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bknight

Brian Knight
Free SQL Server Training Webinars