Hi guys,
I'm trying to create an asp .net page to backup and download an mssql db from my remote host on a shared server. I tried using SMO to create a backup and save it the same folder as the .aspx page but it throws an access denied exception, I later found out that the sql server doesn't have permissions to write backups anywhere but its local backup folder which I can't download from.
Is there any alternative method to get this working?
Can't you have the ASP.NET page read the file from the backup location and stream the file to you?
Of course, you might have to talk to your host about this. They could be explicitly blocking your web site from accessing the SQL Server folders. Why won't they just provide a backup for you? The hosts I've used for shared SQL Server hosting have no problems doing this.
You may try to use SSIS to create package finish backup job, finish file transfer. Then you create a web service to communicate with SSIS package. Then use your web application call web service to start all action.