I have virtual hosting with SQL Server. I don't have physical access to SQL Server. I want to make backup and download it. How can I do it from asp.net page?
A:
You could issue the BACKUP commands from ASP.NET the same way as you would any other SQL query or command.
http://msdn.microsoft.com/en-us/library/ms186865.aspx
- Construct a unique name for the backup file
- Connect to SQL and issue the relevant BACKUP commands
- Check the backup was successful, the backup file exists and stream it back to your browser (using Response.TransmitFile)
Andy Shellam
2010-04-09 13:44:16
but what are the chances that he has access to any of the drives on that remote, hosted SQL Server machine - really?
marc_s
2010-04-09 15:11:33
Depending on the provider, it is common that there is a directory for allowing "asp.net uploads" and/or a location where .mdb files are read/writeable (for legacy apps) which the account running his website has write access to. You could try to use that path.
Nate Bross
2010-04-09 15:18:38
@marc_s - a good point, but we cannot know for sure whether the OP does or does not have access to the filesystem of the box based on the little information we were given. If he does not, then the hosting provider must provide a way to create/download backups. We were told he has no **physical** access to the box - that doesn't mean he doesn't have RDP access or a means of using SQL Management Studio.
Andy Shellam
2010-04-12 08:54:02
+2
A:
Hi Kusanagi,
Even if you do not have physical access to mssql your hosting provider may have a way through your control panel GUI to make a mssql backup and download it. You might want to check with them first.
Jon
Jon
2010-04-09 13:46:04
+1
A:
I think some people here are taking mental shortcuts and suggesting you backup the SQL as if you had full access to the machine (which you specifically state you do not have).
You will not be able to do a standard backup to C:\Backup or C:\[anything] on a hosted environment.
You need to see if you can remotely access your SQL Server instance through SQL Server Management Studio (I have used hosts that do allow remote access to the SQL Server port), and then, instead of doing a normal backup to a BAK file (which implies having access to a local hard drive in the SQL Server), right click on the database and try to create a script from there. You will get a script similar to the scripts that mysqldump creates for MySQL.
If that fails, then look into your control panel, they may have a tool that does that as well, or one that sends you a BAK file generated through the control panel.
Failing that, ask your host.
gmagana
2010-04-09 13:55:37