Database restore from .bak

Question

In my current project there is a need to restore a database using wix installer, previously I had managed to work-around this is issue by creating a .sql script of the DB which was 5-10MB.

Now I have a .bak file around 1GB and with .sql is 2.8GB, I tried adding the .sql to the project but it won't even compile.

Any ideas on how I can solve this issue?

The RDBMS is Microsoft SQL Server 2014 Express.

Answer 1

The best approach for this is to use SSDT for the schema and then use bcp to export the data into CSV files that are then installed to a location on disk. Once they are there you can then use the sql:script or sql:string wix elements to call the sql bulk insert command to populate the data:

bulk insert [Database].[dbo].[TableA] from C:\InstallLocation\TableA.Data.asc

You will need to either make sure that the data is imported in the correct order so that you don't get FK errors or temporarily disable the foreign keys in the db:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

just don't forget to re-enable!

This is actually a very efficient way of distributing a DB to be installed. For example we have a db that is ~1Gb in size and when using the standard mszip cab compression mode in wix the installer ends up ~60Mb. If the compression is switched to high it ends up ~40Mb (but takes quite a while to build!). Obviously YMMV as it will depend on the data being compressed but whatever it will be significantly smaller than a sql backup or sql scripts.