1

I have an ASP.NET web app on a shared hosting server and a SQL Server database on a database server. I would like to backup and restore the database programmatically in the web app where the site owner can download/upload the backup file from a browser. Looking for a solution which works in medium trust. I think SMO (shared management objects libraries) requires full trust and not sure if it even meets my needs. Writing files on the database server, using a control panel or SSMS is not an option.

While I might be able to write SQL Scripts to do all this work, it's going to take some time to have it work reliably for any database. I was wondering if these kind of scripts or a suitable library is available.

Tony_Henrich
  • 42,411
  • 75
  • 239
  • 374
  • Without a way to write to an accessible location on the database server machine you will be unable to run the `BACKUP DATABASE` command. You'll have to speak with your webhost if you want a proper backup file. The only option is to dump all of the data using `SELECT` commands, and that isn't going to be pretty. – Dai Nov 09 '15 at 08:11
  • 1
    I need this to be done programmatically and the host won't provide access to the database file system. I know it's not pretty, hence my question. Looking for someone who went through a similar situation and created a solution. – Tony_Henrich Nov 09 '15 at 08:17

1 Answers1

1

Based on the shared hosting, and the desire to get backups on-demand, you may need to actually run a SQL query that essentially iterates all the metadata and raw data and generates a bunch of SQL INSERT statements.

The good news is, this is a well-solved problem. See this question: What is the best way to auto-generate INSERT statements for a SQL Server table? or just Google "SQL script generator". If you look at some of the open source projects out there, that might be the easiest code to integrate.

With the caveat, it might be worth giving your hosting company a call and just explaining your needs, especially if they are a smaller company. I used to be Dir of Engineering for a small web hosting company and we'd setup ad hoc solutions for folks all the time.

Community
  • 1
  • 1
modal_dialog
  • 733
  • 5
  • 12