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
migrated from stackoverflow.com Mar 22 '10 at 4:23
This question came from our site for professional and enthusiast programmers.
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:
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.
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.
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:
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.
backup database MyDB to disk=' .... '
where .... - path on SQL Server (provided by your hosting provider). This path must be accessed by HTTP or FTP.