locked
Backup to remote shared folder RRS feed

  • Question

  • Hi friends

    I am using SQL server 2008 actually I have migrated from 2000 to 2008. In SQL 2000 easily I could make a remote backup by changing destination from local storage to network shared folder “\\192.168.1.1\share or \\192.168.1.1\d$\backups” but in 2008 it is not possible to address destination over network in maintenance plan. I will be appreciated if anyone could help me to solve this problem.

    Regards

    Sunday, January 5, 2014 9:12 AM

Answers

All replies

  • Hi friends

    I am using SQL server 2008 actually I have migrated from 2000 to 2008. In SQL 2000 easily I could make a remote backup by changing destination from local storage to network shared folder “\\192.168.1.1\share or \\192.168.1.1\d$\backups” but in 2008 it is not possible to address destination over network in maintenance plan. I will be appreciated if anyone could help me to solve this problem.

    Regards

    Are you sure? I just tested on SQL 2012 and gave a location which can be opened via service account. When i gave some location, i got error.

    TITLE: Maintenance Plan Wizard
    ------------------------------

    The Database Engine service could not resolve the specified folder location. Either the location does not exist, or the current login account does not have access to it. Verify that the folder exists, and that the login account has permissions on it.

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Sunday, January 5, 2014 9:35 AM
  • Also - Try without $ share.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Sunday, January 5, 2014 9:35 AM
  • Maintenance Plan are based on SSIS package (SQL Server Integration Services), so you can use Visual Studio 2008 and create a new Project SSIS, automatically it will create a new Package, so drag and drop Backup Task and File system File.

    Therefore for deployment follow this tutorial : http://technet.microsoft.com/en-us/library/ms140117(v=sql.105).aspx


    Sunday, January 5, 2014 9:52 AM
  • I am using SQL server 2008 actually I have migrated from 2000 to 2008. In SQL 2000 easily I could make a remote backup by changing destination from local storage to network shared folder “\\192.168.1.1\share or \\192.168.1.1\d$\backups” but in 2008 it is not possible to address destination over network in maintenance plan. I will be appreciated if anyone could help me to solve this problem.

    I also tried :

    BACKUP DATABASE DSDB_CN TO DISK = '\\192.168.1.1\backup\DSDB.bak'

    GO

    and reply this error:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device '\\192.168.1.1\DSDB.bak'. Operating system error 53

    (failed to retrieve text for this error. Reason: 1815).

    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Regards

    • Merged by Fanny Liu Tuesday, January 7, 2014 8:18 AM duplicate
    Sunday, January 5, 2014 12:33 PM
  • Hi,

    i too use SQL server 2008 and i take backup over network om a shared folder it works perfectly, what error you are getting, give full permission to your sql agent service account to shared folder.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Sunday, January 5, 2014 12:35 PM
  • Operating system error 53 (failed to retrieve text for this error. Reason: 1815).

    C:\>net helpmsg 53

    The network path was not found.



    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Sunday, January 5, 2014 12:37 PM
  • Hi;

    Yes actually I got this error in wizard:

    TITLE: Maintenance Plan Wizard
    ------------------------------

    The Database Engine service could not resolve the specified folder location. Either the location does not exist, or the current login account does not have access to it. Verify that the folder exists, and that the login account has permissions on it.

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    user is domain administrator

    thanks

    Sunday, January 5, 2014 12:39 PM
  • I also tried :

    BACKUP DATABASE DSDB_CN TO DISK = '\\192.168.1.1\backup\DSDB.bak'

    GO

    and reply this error:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device '\\192.168.1.1\DSDB.bak'. Operating system error 53

    (failed to retrieve text for this error. Reason: 1815).

    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.
    Sunday, January 5, 2014 12:42 PM
  • In addition  in the Run command type \\192.168.1.1\ do  you see a shared folder named backup?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 5, 2014 12:51 PM
  • NET HELPMSG 53 from the command prompt gives:

    The network path was not found.

    So the SQL Server machine is not even able to find the server where the share is located.

    Open a command-line window on the box where SQL Server and use ping to see how that fares.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, January 6, 2014 9:48 AM
    Sunday, January 5, 2014 12:59 PM
  • You already have a thread on this in the Security forum. This is not likely to be an issue with the SQL Server version, but a fact that your new SQL Server machine is configured differently. (And since the error code indicates that the IP-address cannot be accessed at all, it seems like a basic problem like the network cable not being connected.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 5, 2014 1:04 PM
  • try to access

    start->run ->\\192.168.1.1\backup


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page



    Sunday, January 5, 2014 1:06 PM
  • refer this post

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/965cd9c4-f4d5-4492-a202-f09b78d89ed1/how-to-backup-a-database-to-a-network-drive?forum=sqltools


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Sunday, January 5, 2014 1:25 PM
  • hi;

    yes i see and full access for its files

    Sunday, January 5, 2014 1:26 PM
  • It's so easy to take SQL server 2008 database backup to shared destination, you just have to have a physical shared folder on a network and take that shared path copy and when you open following window you can paste shared path that you want in "File name:" text field in below window as follow. 

    \\SharePath\filename.bak

    Sunday, January 5, 2014 1:29 PM
  • what is you sql server service account, is it domain account or local account , if it is domain account and if you have given full access to this account on remote shared folder and still not able to backup o that folder then make sure your SPN authentication working properly, refer below article for ore info.

    http://technet.microsoft.com/en-us/library/ms191153.aspx#Manual


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Sunday, January 5, 2014 2:57 PM
  • Try to run the command below

    EXEC xp_cmdshell 'dir \\192.168.1.1\d$'

    If you are getting the result then it's good. If you are getting error  53 then make sure the share is created properly. You can get the use the DOS command NET SHARE. Make sure you see the share, by default $ shares are created during OS installation and only administrators has access to it. So make sure your SQL Server service and SQL Server agent service account has modify rights to that share. If these things are good you should be able to take a backup to the share.

    http://sql-articles.com/articles/general/backup-sql-database-to-remote-location/


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Monday, January 6, 2014 9:54 AM