Backup SQL Database but save BAK locally?

[Administrator]

Using SMO, etc. when you create a backup the backup is on the "device" i.e. the server that runs SQL Server or connected storage devices (tape, disk, etc.). However, for people using shared web hosting, etc. they may want to keep their SQL Server backups on their local computer. Is it possible to create a SQL Backup and have the backup data brought across the wire and saved on "my" computer?

 

[Arg81]

Neal,

Unless I read what you are trying to do wrongly, you can change the location of the .Bak file in the Database Maintenance Wizard in the Enterprise console.

I just set up a shared folder on my PC called Temp, and then on the wizard, set it to backup to disc, with the location being \\it2\Temp - it accepted it fine. If you click "browse", you only see local (local being the SQL server) drives.

Hope that helps.
Luke

 

[Administrator]

Hi Luke

The scenario is a smart-client application connecting their local Winforms app to a remote SQL server via direct SQL communication or Web Services. So the SQL Server is a leased service by another company, not on the users network. What I need to do is create a backup and have this backup delivered to the local computer.

If I could "backup to a stream" per se and then deliver the stream across the wire that would be nice, but again, I don't have any information as the SQL Server is a "leased service" so I'd have to play with options such as trying to create a .BAK to the working folder of the web service with a unique name (guid) and then download it programmatically.

The ultimate answer is coming in Orcas with synchronization services where I can have a local data store and just port the data from the SQL Server to the local store. Probably slower, but an option.

 

[Arg81]

ah, didn't realise it was on a leased service

The data store does sound the best option, but obviously not available yet!!