Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I havea 70MB db of my website which is hosted with a provider. I am able to access my db using SSMS 2008 remotely.

On a running website, which is the best way I can back up the db locally on machine

Thanks

share|improve this question

migrated from stackoverflow.com Mar 22 '10 at 4:23

This question came from our site for professional and enthusiast programmers.

3 Answers 3

You always use BACKUP DATABASE for backups, but this will only go the local volumes (drives, SAN etc) or a network share.

Your question is unclear though:

  • Your SSMS access if after remote access onto their machine: not from your local machine?
  • Do you mean "backup to my local machine"?

If you can access the remote SQL Instance from your local machine, I'd say change hosting provider now. If you want a backup stored locally on your machine, then you'd need to have a file connection between here and there.

Edit:

Use tools like Red Gate SQL Compare and Data Compare to create a copy on your local machine if you can access the DB. You won't be able to backup to file over the internet (I hope.)

This also means your SQL Server instance is exposed on the internet which is a bad thing generally which is why I said change providers.

And yes, the web site can stay running.

share|improve this answer
    
sorry if that was not clear..i can access the db hosted on my webhost provider from my local machine and yes i want to take a backup on my local machine. Why do you say "If you can access the remote SQL Instance from your local machine, I'd say change hosting provider now". Is it ok to take a back up on a running site –  Veejay Mar 21 '10 at 12:10

There are two tools you can use to do the job remotely:

Neither of them can generate a valid SQL Server backup, but they can transfer data and schemas between databases over the network. Additionally, both tools have the following features:

  • platform independent, will work anywhere Java runs
  • scriptable (can be scheduled etc.)
  • database-independent, to a large degree, supporting a wide variety of DB systems
  • cross-database support: mirror a database from mssql to postgresql to oracle to mysql...
  • free and open source

and limitations:

  • they might have limited support for stored procedures, triggers and other DB code: they are mostly focused on data and data structures
  • might require some hand tweaking to work (something might be a keyword in one database system, but not in another so it has to be treated differently etc.)
  • you need a local SQL server instance as a target to which data will be copied

Having said that, it is really mind-boggling that in 2010 there exists a relational DB system which doesn't provide a way to execute an over-the-network backup and will not work on anything other than Windows. And is relatively widely used.

share|improve this answer
    
Thanks for your reply..I was thinking can't I just generate scripts using 2008? I mean that can be done remotely right! –  Veejay Mar 21 '10 at 12:23
1  
Of course SQL Server can backup over the network, but this is over the internet from a provider. Do you understand what that means? The fact it's SQL Server/Windows is irrelevant: I wouldn't want any DB of mine exposed onto the internet because there is no need for it. –  gbn Mar 21 '10 at 12:30
    
The database can be set up to be reachable from a set of IP addresses, without security compromises. The fact remains that SQL Server (any version, as far as I know) does not provide a way to save a backup on a local disk of a remote machine. In some cases, it's possible to map a network drive or use a specially created backup device with a UNC path to backup to, but it simply means you're stuck when the SQL Server is running on an ISP and you're stuck if you don't have a CIFS server on the target machine. To be fair, it can generate scripts to regenerate the schema and data like ddlutils can. –  Tomislav Nakic-Alfirevic Mar 21 '10 at 14:59
    
A local disk of a remote machine is a network share. The problem here would be the provider not SQL Server. Are you that biassed? –  gbn Mar 22 '10 at 5:25

backup database MyDB to disk=' .... '

where .... - path on SQL Server (provided by your hosting provider). This path must be accessed by HTTP or FTP.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.