RESOLVED - create sql bacpac from asp.net code

[BruceG]

Hello. The situation is, we have an ASP.NET application running on a shared hosting site. I would like to be able to somehow run a backup of the SQL database on a regular interval (say once per day), and have that backup ultimately reside in an area of the local website storage - i.e. "Server.MapPath("MyDBBackupFolder"). When I say "backup", this can be, but is not limited to, a standard SQL "bak" file, as bacpac would also be good, and if all else fails plain text script statements for create and insert.

The host does not allow outside (remote) access to the SQL database (so I can't get to the DB via SSMS - I have to use their control panel tool MyLittleAdmin, but that is besides the point). Also, the host does not provide the ability to set up scheduled, automated backups of the SQL DB. (They do provide the ability to run a SQL backup manually thru their control panel.)

I would like to know if it is possible to do something on the client website side (i.e., the directory where my aspx pages reside) to either create a bacpac, backup, or even a set of scripted create/insert commands. The website/aspx of course connects to the database as it needs to do to run the application.

However, to do a command such as "BACKUP DATABASE MyDb TO DISK = 'C:\blah .." would be to no avail, since the target refers to the server on which the SQL DB resides, which the aspx application does not have direct access to. But of course the aspx application does have access to its own "local" storage ("Server.MapPath("blah")).

The ideal thing here, if possible, would the ability to create a SQL backup, or bacpac, thru the aspx application, where the output destination is the local (Server.MapPath) storage.

Failing this, would there be an appropriate scripting solution that you know of that would iterate thru each table and generate the create table and insert statements and save it off to a text or zip file? (I know that this could be "home-grown", but I would prefer not to have to write that myself if there is an existing utility/library/nuget package that can do this).

I hope I have presented the problem clearly and look forward to any proposed solutions ...
Thanks,
Bruce

[wqweto]

Did you try running sqlpackage.exe utility from your ASP.Net page like explained in Export to a BACPAC file (first hit for "how to create bacpac file" in google)?

FYI, this worked here with a local SQL Server instance (non-Azure):

c:> sqlpackage.exe /a:Export /tf:MyDB.bacpac /scs:"Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI"

The utility is in C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150 on my machine and depends on a ~10 DLLs which you should somehow transfer to your ASP.Net host if it's not preinstalled there already.

cheers,
</wqw>

[BruceG]

Thanks for this. The problem is (I believe) running an executable on a shared hosting site. What I have done thus far is run a test for this locally, and as I expect, sqlpackage runs fine. (Behind a button on an aspx page, I am running the exe via System.Diagnostics.Process.)
I copied the sqlpackage executable and related binaries over to the server along with my test page, expecting to get an ugly yellow screen of death permissions error. When I ran it, I did not get any error message, but it did not work. There was no error notification, but the output was not generated either.

[wqweto]

Another option is to reference C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\Microsoft.SqlServer.Dac.dll assembly and call DacServices Class's ExportBacpac method from you ASP.Net page.

Here can be found some hodgepodge sample code.

cheers,
</wqw>

[BruceG]

Thank you - I will check it out and report back here once I have given it a try.

[BruceG]

Hello - I know it's been a while, but was finally able to resolve this. Thanks to wqweto for pointing me in the direction of the DacServices class. Using DacServices, we initially tried to do this using its"ExportBacpac" function but ran into an issue due to an "orphaned user" in the DB. We then tried its "Extract" function (which creates a dacpac rather than a bacpac) which happily provides and option to "IgnoreUserLoginMappings" - and that worked like a charm.