1

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.

Dimitris Batsougiannis
  • 739
  • 4
  • 14

1 Answers1

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.

caveman_dick
  • 6,302
  • 3
  • 34
  • 49
  • interesting approach. I'm still new to wix so i'm learning. I'll check on it. If you have any links / resources that can help please post then. hny mate! – Dimitris Batsougiannis Jan 05 '17 at 19:00
  • 1
    Restoring database .BAK is certainly not a best practice. I've seen tons of issues trying to restore them to different versions and editions of MSSQL. Also you don't leverage any SCM history doing it that way. – Christopher Painter Jan 06 '17 at 00:02
  • @ChristopherPainter can you elaborate on your answer. Using the .bak file seems the most easy solution right now. Also what do you suggest about it – Dimitris Batsougiannis Jan 06 '17 at 19:19
  • Database owner, logins, restoring to lesser editions or versions can all cause problems. But here's a simple problem. What if you need to add a table/column in a later release? Are you going to drop and restore the database again? Best to just get started scripting from the beginning. – Christopher Painter Jan 06 '17 at 23:31
  • @ChristopherPainter wouldn't in that case be possible to add in an SqlScript tag the respective sql code to do the alterations. – Dimitris Batsougiannis Jan 09 '17 at 00:53