Post reply

Unable to backup on Network

  • February 17, 2006 at 9:28 am

    #112715

    Hi,

    I've been trying to backup the database from my local computer to another local computer (same workgroup and same ip range). But it doesn't allow me to do so. The database version that i have is SQL 2000 and the O/S is Windows 2000 Advanced Server. The system on which i am trying to take a backup is also Windows 2000 Advanced Server. I am able to access that computer fine and i have also mapped the drive to one of the folders in that computer. There is no firewall in either of our computers and there is full sharing in the destination computer.

    I tried backing up through different ways as follows

    - Backing up through Mapped Drive ("Z:\sharedfoldername\backupname.bak")

    - Backing up by using a full path (\\ipaddress\sharedfolder\backupname.bak)

    - Through hostname (\\hostname\sharedfolder\backupname.bak)

    The error that i receive when i try to backup is as follows:

    ----------------------------------------------------------------

    Server: Msg 3201, Level 16, State 1, Line 1

    Cannont open backup device '\\backupserver\Backups\TestingDB.dat'. 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 know this is a permission problem (its the same thing that even says in error logs - access denied)

    But the database (MSSQLServer service) is running under the main Administrator account and that the account that i am logged in with.

    Is there anything that i am missing here? I did check about this online but didn't find anything that would be helpful to me.

    All it says that one needs to check if there are right permissions (which in this case i guess i have ) and second thing it says is about changing the permissions in registry but i am resisting to go through secondary method.

    If any one has an idea where i could be wrong or what is the solution please help me in this case.

    Thanks

  • February 17, 2006 at 9:32 am

    #621724

    check the permissions for the SQL Server service account on the shared folder..

     

     

    -Krishnan

  • February 17, 2006 at 9:43 am

    #621730

    do u mean that i should check the permissions on the shared folder on the desitination computer? If so then how exactly do i do that?

    Thanks for the reply

  • February 17, 2006 at 11:54 am

    #621774

    This may be off the point, but it's fairly typical to make the backup locally, and then copy that backup (and restore it if you want to) onto the remote machine.

    HTH

    Elliott

  • February 17, 2006 at 3:15 pm

    #621818

    Thanks for the reply Elliott but thats what i have been doing. Right now on my live server i have the schedules that run and take backups locally and then i have another batch file running that copies and pastes on another server on network. But the problem is that the space on that local server is very low when compared so some of the database that i want to backup locally and so this is the next best option that i can go for as of now.

  • February 17, 2006 at 3:36 pm

    #621824

    Is SQL Server running using the System Account or a domian account?

    If it is running under system account then it cannot acces the network resource.. If not then go the shared folder folder properties on destination where u r dumping the backups then under sharing tab check the permissions for the sql server account..

    hope this helps...

     

     

     

    -Krishnan

  • February 17, 2006 at 6:09 pm

    #621846

    It is running under Domain account. Now so far as the permissions is concerned, i check the Sharing tab of the folder where i am dumping backups and in that i clicked the 'Permissions' button and in there its 'Everyone' with Full control.

    Am i supposed to add anything else?

    Thanks

  • February 20, 2006 at 9:09 am

    #622061

    Are you running the backup from SQL Server Agent as a job, or directly from the Enterprise Manager?  If running as a job, check the permissions of the job owner.  If the owner is not a domain account with proper permissions on the file - or  a member of the systems admininstrator role - the job may run with the credentials (and OS permissions) of the SQL Server Agent proxy account.  If the owner is a member of the systems admininstrator role, then the job should run with the credentials of the account used to start SQL Server.

  • February 20, 2006 at 9:38 am

    #622069

    I am just trying to backup by running the backup command from Query Analyzer i.e.

    Backup Database (databasename) to Disk = '\\(ipaddress)\sharedfolder\databasename.dat'

    The account that i am logged in with is the Admin account and its that same account that i am logged in with in the computer from which i am taking the backups.

    One more thing that i did notice is that when i attach an external hard drive (USB) to the computer from where i am taking the backup and if i take a backup in that external drive then i am able to take the backup successfully.

     

  • February 20, 2006 at 1:34 pm

    #622109

    If doing the backup manually, use Enterprise Manager.

    Expand the list and right click on the database and select All Tasks / Backup Database.

    Click 'Add' to enter a backup path and file name (to a mapped drive if required).

    Marc

  • February 20, 2006 at 5:20 pm

    #622139

    Hi Marc,

    Do i create a backup device that is pointing  the path to mapped drive or desitination computer and then select that Backup Device from All Task - Backup Database - Select Backup Destination window or should i just select the file path in 'File Name' option 'cause i tried to take a backup by typing the ip address as well by even typing the Mapped drive but when i finally take a backup, it still backups to the local drive (c only. So i am not sure as if there is a method that i have to follow to take backups from Enterprise manager to backup on a network computer.

    Thanks for the reply

  • February 20, 2006 at 7:42 pm

    #622147

    Hi Grasshopper

    Sorry - it looks like I put you crook with regards to the mapped drives, however, the SQL Help file says this..."If the backup is to be performed over the network to a disk on a remote computer, use the universal naming convention (UNC) name in the form \\Servername\Sharename\Path\File to specify the location of the file. As with writing files to the local hard disk, the appropriate permissions needed to read or write to the file on the remote disk must be granted to the user account used by SQL Server.

    Because backing up data over a network can be subject to network errors, verify the backup operation after completion. For more information, see Verifying Backups."

    I have just attempted to do this to one of our small databases, but I too could not get it to work. I suspect it is a permissions issue for the SA on the network drives.

    Marc

  • February 21, 2006 at 9:48 am

    #622361

    Hi Marc,

    About the UNC name, i have already tried that through QA as well as EM but gives me the same error. In fact in EM when i add the path and then it prompts me with a message saying 'Unable to verify the existence of the backup file location. Do you want to us the backup file location anways?". If i continue with that it give me the 'Device offline message...'.

    And yes as i said before i am able to open the shared folder from the local computer when i type in the same path '\\sharename\foldername' in the 'Run' Dialog Box.

     

  • February 21, 2006 at 2:03 pm

    #622450

    The problem must be in permissions, as the following commands work on my network:

    --Create the backup from QA on Server A

    backup database TestDB to disk='\\ServerB\c$\temp\TestDB.dat'

     

    --Restore the backup on Server B

    Restore database TestDB from disk='c:\temp\TestDB.dat'

    with

    move 'TestDB_Data' to 'c:\mssql\mssql\data\TestDB_data.mdf',

    move 'TestDB_Log' to 'c:\mssql\mssql\data\TestDB_log.ldf'

    Elliott

     

  • February 21, 2006 at 2:05 pm

    #622451

    try logging in using the sql server account and try to access the shared folder..

    Also try executing the backup the cmd as part of a job..

    If you are able to access then it seems the double hop is giving the problem..

     

     

    -Krishnan

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

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