Post reply

backup db to network share computers not in domain

  • November 7, 2011 at 4:38 am

    #251192

    Hi All

    We use a 3rd party for hosting our DB and websites, but the machines are in a workgroup and therefore permissions cannot be granted to other servers for what I am trying to do.

    The DB server needs and upgrade but I am awaiting financial sign off for a new server to be commissioned.

    I asked the 3rd party to restore the BAK file to a web server so I can FTP it to the office to restore on our DEV server, they said they can only restore it back to the DB server, thats alright apart from the fact that the DB server doesnt have enough space for the BAK file to be placed on.

    On one of our web servers I created a share and gave everyone full control to the share, but the DB server wont backup to it, so I created the share as a network drive but still no luck.

    I know I cant add the DB server into local admins on the Web server as they cant see each other through computer manager, but they are on the same private network and can ping, map drives and talk over name and IP, its just share permissions which is the problem.

    Can anyone help with this, don't know if its me or if its Monday morning mind blank.

    Msg 3201, Level 16, State 1, Line 1

    Cannot open backup device 'z:\db1.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 1815).

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

  • November 7, 2011 at 5:19 am

    #1403854

    anthony this is just a guess, but it might work:

    it's possible to map a network drive, and include the username/password to use[impersonate] the permissions for it;

    i've done it with xp_cmdshell in a few posts here as an example...i'm thinking that if you map the drive from the SQL server, using , say, a user on the webservers credentials, it should work:

    exec master.dbo.xp_cmshell 'NET USE G: \\UNCPath\d$ /user:domain\user password'

    GO

    Restore Database DBName FROM Disk = 'G:\PAth to bak'

    GO

    EXEC master.dbo.xp_cmdshell 'NET USE G: /DELETE'

    NET USE path password /USER:domainname\username

    --or

    NET USE path password /USER:localMacineName\username --WebServer1\anthony MyMachinePassword

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 7, 2011 at 6:09 am

    #1403887

    didnt think about xp_cmdshell,

    will enable it and try the backup

  • November 7, 2011 at 6:19 am

    #1403900

    Thanks Lowell, that seems to be working as the BAK file has been created and no error message.

    Must be something in using the NET USE command and providing the user and password, rather than mapping a drive giving it a user and password to connect as that makes the impersonation work.

    Cheers once again.

  • October 11, 2019 at 7:36 am

    #3687936

    Many thanks @lowell, you helped me a lot.

    Just one thing (I was confused, searching, looking again and then laughing at myself), there is a typo in the first line (cmd instead of cm).

    exec master.dbo.xp_cmshell 'NET USE G: \\UNCPath\d$ /user:domain\user password'

    should read

    exec master.dbo.xp_cmdshell 'NET USE G: \\UNCPath\d$ /user:domain\user password'

    (and also, the schema is sys in my case, but that was easy enough)

Viewing 5 posts - 1 through 4 (of 4 total)

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