0

I have a remote database on a shared host, and I can connect to it through SSMS if I specify the db name in advanced options. If I try to expand the "Databases" section of the object explorer, it times out and won't list the available databases.

I need to back it up locally, so scripting it seems to be the only way. However, you need to right-click the db name to do that, which isn't possible. Is there a SQL script that I can run to export a db's structure and data?

Thomas Higginbotham
  • 1,662
  • 20
  • 25

1 Answers1

0

Usually in the control panel provided by the hosting company, there's a "Database" option, and if you go to that, you can find a "Backup Database" (sub)option.

Usually this will back up the database to a folder called "db" or "data" off the root folder of your hosting account. Then you can connect with FTP & download (or upload & restore from there).

An alternative might be to use the "BACKUP DATABASE TO DISK='XXXXXX'" SQL statement. But then you have to know the XXXXXX value to use (and this depends on whether you have the permission to execute this action). The XXXXXX would be the local path of your hosting account, which would probably be something like: C:\domains\yourdomain.com\db

(...depending on how the hosting company has that set up).

Sean
  • 14,359
  • 13
  • 74
  • 124
  • Nope, no backup option, and backing up to disk is out of the question, since it's a remote server, and I don't have access to the files on it. – Thomas Higginbotham Jun 26 '12 at 23:17
  • Have you got SSIS? Or DTS? (On your local machine). In SSIS/DTS, you can transfer database objects from the remote server to your local SQL Server. Or you can copy the tables down. – Sean Jun 27 '12 at 07:02
  • I was finally able to get in contact with the host, and it turns out that there was a backup option hidden in an obscure section of the control panel. – Thomas Higginbotham Aug 17 '12 at 12:20