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 cant 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.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?|||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.|||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.|||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 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.