SQL BACKUPS

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 09:58

Hey Peeps.

I need some help regarding SQL backups. WE installed an INtel Baxter creek storage system for a client. IT's a little server box with 4x500GIG SATA II drives.

I mapped a network drive to a folder I created on this storage system.

I can browse to it normally, create folders etc... SO for normal windows use its 100%

BUT. I can't find a way to backup my SQL databases to the said network drive. IF You take this route, righclick on the database --> All tasks --> Backup Database, you get to the backup screen.

But my only options when adding a new backup here, are to local drives.Only the Server's C and D drives are present and not the Mapped Z: Drive. IF I try and browse to the location by entering z:\ manually it says "SQL cannot connect to the drive(bla bla)..........."

IF you browse backup locations/destinations, as many of you would no, it does not display a folder tree, only the local drives.

No network drive.

Any help on this please!??!?!?!?!?

rustypup · Post by **rustypup** » 03 Apr 2007, 10:06

it probably requires the resolved path, not the 'masked' entry provided by explorer.. (the "mapping" just displays a more user-friendly id...).

have you tried

Code: Select all

\\server\shared_folder\

??

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 10:13

YEah dude, tried that aswell.

rustypup · Post by **rustypup** » 03 Apr 2007, 10:26

the server user, (under which the backup agent is being run), has full access rights to the share?... (i'm presuming a resounding yes... )

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 10:37

UM, LOL! YEAH MAN.

THE Administrator on the account has full privelages everywhere.

AND as mentioned before, if you browese to that directory from Start--> Run, or using hte Z: drive, you immideatly have full access.

I created various test folders to check, I tried connecting there using IpAdress\share, Mapdrive and pcname\Share, still nothing.

I included screenshots to show you the setup, and the error....

rustypup · Post by **rustypup** » 03 Apr 2007, 10:45

odd..

have you tried specifying a file?

Code: Select all

\\server\folder\backup.bak

also, i know its internal.. still, blot that ip in pic1...

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 10:47

LOL.

Let me try the file thing quick.........

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 10:52

Same thing.....

I just did a test backup of a folder to a local drive, and i just used d:\test\test.file, and the destination e:\backup\test

and it went.....

SO no need to specify the exact FILE to backup to.........

rustypup · Post by **rustypup** » 03 Apr 2007, 11:12

this points to an issue with the management app... we run several different sql management tools here, accessing seven servers, and roughly as many shares, none of which present with this.. only the one requires a resolved path...

nothing under support for this type of issue?... 8O

<feels vaguely useless in the face of overwhelming oddity... >

viceroy · Post by **viceroy** » 03 Apr 2007, 11:37

Have you tried creating a backup device pointing to the shared folder and then backing up to the backup device?

Thats how we do it.

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 11:50

Ok, I tried that now...... Still the same thing.

THis is uber wierd.

viceroy · Post by **viceroy** » 03 Apr 2007, 11:56

how EXACTLY did you do it?

Post by **Scarlet_Spider** » 03 Apr 2007, 11:57

i had this a while ago, just ended up backing up to a local drive and then creating a scheduled task to run a bat file that used xcopy to move the backup across and when done just delete the file from the local drive (if desired)

long way 'round, but it works...

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 12:05

I was thinking about going that route, but THe client (and the Database developer, whom himself cant get the backups going) Demands it to be done this way, cause its part of hte software security policy that they drew up.

so I HAVE TO DO IT THIS WAY.

PLus the fact that normally copying it in windows, while users are logged into the DB will cause it to crash the copying/backup....

ANd the database is over 100GIG's big, so its not something htat's just gonna take 3 minutes........

viceroy · Post by **viceroy** » 03 Apr 2007, 12:06

viceroy wrote:how EXACTLY did you do it?

Creating the backup device.

Also...Are you SURE the account doing the backups has permissions to the shared folder?

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 12:13

viceroy wrote:how EXACTLY did you do it?

I tried to create a backupset (and backing up normally) like this:

\\192.168.0.205\Public\backups\SQL (with and without a slash at the end)

and

z:\ (Drive Z I mapped to the full path, but what I also did was only map it to the public folder, to test, so with that I went)

z:\backups\SQL (again with and without a slash at the end)

I also tried

\\backupserver\public\backups\SQL

ANd through each and every setp I did the same thing via windows and again, I have FULL access to Z:

I have (or had when I did it) full access to z:\public

I have full access to 192.168.0.205\ and to every foler inside

Cause the server has 4 HDD drives, I tried mapping, and jsut plain sharing the drives and folder in them independantly, also get hung up on the same problem..........

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 12:14

viceroy wrote:
viceroy wrote:how EXACTLY did you do it?
Creating the backup device.

Also...Are you SURE the account doing the backups has permissions to the shared folder?

YEah man, The Administrator account has FULL PRIVELAGES EVERYWHERE, read the post above......

I did try, just for interest sakes to to use another power/superuser with full access to do it, and got the smae issue.

viceroy · Post by **viceroy** » 03 Apr 2007, 12:14

Okay, thats fine...but did you create a Backup Device like I suggested? It doesn't seem like if from your description

And you haven't specified the file to use for the backup...do it!

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 12:23

viceroy wrote:Okay, thats fine...but did you create a Backup Device like I suggested? It doesn't seem like if from your description

And you haven't specified the file to use for the backup...do it!

Yebo I did......

This is what I did there.......

NOw here is the path.

Again, I tried, Name, IP and mapped drive, and even different folders (wich do exist and I have full access to)

IF you click the ... (browse button) only the 2 local drives of the machine appear.....

I did specify file aswell, I called it 100 backup.bak.......

viceroy · Post by **viceroy** » 03 Apr 2007, 12:29

Then I'd suggest checking out the NAS device because it's not SQL

Maplassie_GTR · Post by **Maplassie_GTR** » 03 Apr 2007, 12:35

Hmmm.

Its rather strange.

I logged into 5 computers remotely, and copied 1 gig of data each, to the above mentioned folder (cause what I did now was gave EVERYONE complete access) and it went though fine....

I know its not SQL, it most probably is something I am missing completely here, but what

rustypup · Post by **rustypup** » 03 Apr 2007, 13:09

bet you 50 cheese cubes the errorlog reports authentication issues... try replicating the user on the backup/share server directly, (bypassing share replication)....

could be the management app is not using the windows authentication service correctly, which could be the root of the issue...

this is a first and i'm dying to see the cause

Slasher · Post by **Slasher** » 03 Apr 2007, 13:15

Wow... I was thinking that whole thing as well rusty... (No, this is seriously honest...)

I am also thinking that SQL does not check for user access according to the windows setup, but works on a SQL login basis. Thus, you would not be allowed to access the mapped drive then...

viceroy · Post by **viceroy** » 03 Apr 2007, 13:16

Slasher wrote:Wow... I was thinking that whole thing as well rusty... (No, this is seriously honest...)

I am also thinking that SQL does not check for user access according to the windows setup, but works on a SQL login basis. Thus, you would not be allowed to access the mapped drive then...

SQL does allow windows and domain accounts to access it if using mixed mode auth

Maplassie_GTR · Post by **Maplassie_GTR** » 11 Apr 2007, 14:35

WEll Um. You would not believe that I am still struggling with the issue.

Thing is I even got hold of a buddie's buddie, who is really awesomely trained in the arts of SQL, and not even he could get it right. its very strange.

I opted to go the long route, by copying it to a local HDD, then copying to the backup device afterwards. Takes uber long, but it works so........

ANyhoo, shot for hte help and the input!

Cheers peeps