Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: Backup of Remote SQL 2005 Server

    Hello Friends:

    My problem relates to backing up my MS SQL 2005 database which is sitting on a shared server at a hosting company.

    OVERVIEW:
    - Hosting company is using MS SQL 2005
    - I am using the SQL Server Management Studio that comes with SQL Server 2005 Standard (NOT Express), which is installed on MY PC.
    - So, I am connecting to the SQL server over the internet


    WHAT I WANT TO ACHIEVE:
    - I would like to backup the data sitting on the Hosting company's MS SQL Server. I only have one database on this SQL Server. There are of course 100s of other databases on the same server which belong to other customers of the hosting company.

    - I want to bring the backup to MY PC, from the SQL Server.

    - As far as I can tell the following options within SQL Server Management Studio may be of help to me. I do not know which one I should use or which one is best or what is the proper method. 1) Select Backup option from the Tasks menu (but it only shows me drives/devices on the Hosting SQL Server, not my PC, so I can’t backup to my PC) 2) Export Data (it does not work, showing errors ‘…not a trusted connection…’ I have no clue what a trusted connection is. 3) Copy Database (which is supposed to copy the remote database on the Hosting company SQL server, to my local SQL Server running on my PC). I go through the wizard, on the last screen it just hangs i.e. shows- not responding)


    MY QUESTION TO THE COMMUNITY:
    How do I backup the database sitting on the hosting company SQL server? I of course need to bring the backup to my PC.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    What you'll need to check is whether you have access to a folder from SSMS which also can be accessed by other means, eg FTP. If so, you can run a backup to that folder, and then download the backup file. SSMS itself does NOT give access to your local disk drives.

    But, why do you want to backup the database yourself, does not the hosting company take backups for you?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    May 2007
    Posts
    23
    Management studio does not support the UNC path, generally it will show you the local disk to the server. To perform network back up you need to write the TSQL command.
    The username by which you login into CMS, requires permission to perform back up and restore. Basically the Admin of hosting company will assign permissions to each and every user. Speak to hosting company Administrator for help.

  4. #4
    Join Date
    Jun 2007
    Posts
    2

    Remote Backup of SQL data

    Thank you all for your quick reply. My ISP does perform a backup. Then why do I want to do a backup to my local PC?

    Well, I am planning to move to a new ISP. And so I need to move my database also. I first need to backup the SQL database (or extract the data) from my existing provider. Then I need to somehow upload the database to the new provider.

    I am sure this is a very common issue. People must on occasions move from one hosting provider to another, and if they have a database they will not want to lose all the data.

    Since I posted my first message, I signed-up with 3 ISPs. It did not work with 2 of them, and tech support could not tell me what to do. Then I signed up with Network Solutions, and I can now at least Export the data.

    Copy Database still does not work. But I think I know why. Since there are 100s of other databases on the same server (and I do not have access to them) the Copy Database wizard probably tries to access them while displaying the list (which it does when it asks you for the Source Database).

    I think a user with Sysadmin rights would be able to run Copy Database, but getting those rights of course would not be possible in a shared hosting environment.

    Over the last several days, I have called the Tech Support of most major hosting companies in US, UK, CA, and some countries in Europe. I was able to speak to 'real tech humans' in each company. But surprisingly NOT EVEN ONE could answer my questions, or even shed some light on the issue. They were completely clueless!!

    It seems either no one changes hosting companies or no one has any important data to backup or everyone who knows about these issues uses only dedicated servers (which cost lot more money). Either way, the tech support in these hosting companies (most of whom have at least 200 employees and $4million+ turnover) had little idea about what I was talking about. Network Solutions seems to be the best and has techs available 24X7, and atleast they all speak clearly and try to help.

    By the way I have been in IT for 20 years, I did make sure I was asking the questions clearly.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    why not create a share from the host to your machine, open a query session and use t-sql to backup the database to your machine?

    Why does it have to be GUi based?

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jun 2007
    Posts
    1
    This might be an easy answer to a complicated question/issue--- but I've used a free tool for backup that has proved to be actually high-performance stuff... can be downloaded free at: www.idera.com/freetools, and its called SQLsafe Freeware Edition.

    Hope that can do the trick, why not, its the right price... free. ha!

    Enjoy, and let me know how it works for you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •