Backup Shared Server DB using SQL Server 2005 Management Express

Hi,

I want to backup and/or have a local copy of my DB which is on a shared host. I have an ip addreess and user/pwd, but I am at a loss to find any help for the steps to accomplish this in Management express.

Am I using the right tool?

You will have to keep in mind that backup / restore will only either reach local drives or accessible UNC shares. I do my backups using the Copy Database wizard in management Studio, but if you only have SQL Server Express you can only use the SQL Hosting Pusblishing kit to destribute the database to a hoster and not the way back unless you local database is also published to the internet and the remote server is SQL Express.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks.

So what is the recommended method/product to take a backup of a SQL DB from a shared host that uses a SQL 2005 Server?

|||I would recommend buying a CAL of SQL Server to have the advanced tools for importing data including the copy database wizard.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

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.

Backup From SQL server to a Backup server

Hello,
We are currently running our SQL backups to our local disk drive. Now we'd
like to change the path from local to a shared folder on another server on
another domain.
Is that possible?
Thanks,
Greg
It is very possible. We do it all the time. One tricky thing we ran into
with ours, we had have both SQL services run by the same DOMAIN account for
the job to dump directly to our network store box.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:471BE8E4-B85F-4A77-BD97-95267B6A34E8@.microsoft.com...
> Hello,
> We are currently running our SQL backups to our local disk drive. Now we'd
> like to change the path from local to a shared folder on another server on
> another domain.
> Is that possible?
> Thanks,
> Greg
|||You can run backups to a network share - you'll have to make sure that the
SQL Server/Agent Services have access to that shared.
However, if at all possible, I would suggest against it for a couple of
reasons:
1) It will increase that amount of time the backup takes as well as
resources the backup uses to backup the database because you are doing it
over the network.
2) It will increase the possibility of the database backup getting
corrupted. Being that that backup is happening across the network you
increase the likelihood of something happening. I would suggest (if disk
space is a problems and that's why you are doing this) doing the backup
locally and then copying that backup off the machine, you decrease that
likelihood.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:471BE8E4-B85F-4A77-BD97-95267B6A34E8@.microsoft.com...
> Hello,
> We are currently running our SQL backups to our local disk drive. Now we'd
> like to change the path from local to a shared folder on another server on
> another domain.
> Is that possible?
> Thanks,
> Greg
|||Thanks! Were could I find the SQL Server/Agent services? Is it under services?
Thanks,
Greg
"slamm" wrote:

> You can run backups to a network share - you'll have to make sure that the
> SQL Server/Agent Services have access to that shared.
>
> However, if at all possible, I would suggest against it for a couple of
> reasons:
> 1) It will increase that amount of time the backup takes as well as
> resources the backup uses to backup the database because you are doing it
> over the network.
> 2) It will increase the possibility of the database backup getting
> corrupted. Being that that backup is happening across the network you
> increase the likelihood of something happening. I would suggest (if disk
> space is a problems and that's why you are doing this) doing the backup
> locally and then copying that backup off the machine, you decrease that
> likelihood.
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:471BE8E4-B85F-4A77-BD97-95267B6A34E8@.microsoft.com...
>
>
|||Yes, you can find it under Control Panel; Services.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:9B5E5A81-06E0-4E05-8242-DB94C89ABBA6@.microsoft.com...
> Thanks! Were could I find the SQL Server/Agent services? Is it under
services?[vbcol=seagreen]
> Thanks,
> Greg
> "slamm" wrote:
the[vbcol=seagreen]
it[vbcol=seagreen]
we'd[vbcol=seagreen]
server on[vbcol=seagreen]
|||Thanks but how can I give SQL services access to the shared drive on another
domain?
Greg
"JD" wrote:

> Yes, you can find it under Control Panel; Services.
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:9B5E5A81-06E0-4E05-8242-DB94C89ABBA6@.microsoft.com...
> services?
> the
> it
> we'd
> server on
>
>
|||Your network department can do that. They'll need to know what domain
account you want to use for the services and what access it needs to write
the backups to another server/drive.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ED53BE72-5090-45D0-8FFA-C81BC16D4100@.microsoft.com...
> Thanks but how can I give SQL services access to the shared drive on
another[vbcol=seagreen]
> domain?
> Greg
> "JD" wrote:
that[vbcol=seagreen]
of[vbcol=seagreen]
well as[vbcol=seagreen]
doing[vbcol=seagreen]
getting[vbcol=seagreen]
you[vbcol=seagreen]
disk[vbcol=seagreen]
backup[vbcol=seagreen]
that[vbcol=seagreen]
Now[vbcol=seagreen]