none
sql server database in msi

    Question

  • BH

    Hi,  i've created a database in ms sql server 2008 express.  How can i distribute with my software?  Do i need to create a backup and restore of the database? or is there a better way? 

    Thanks

    Aron


    Thanks Aron www.EvidenceForTorah.comxa.com

    Thursday, February 23, 2012 2:38 PM

Answers

  • Hello,

    There are a few different options.

    1. Backup and Restore
    As you have already mentioned, it is possible to take a backup the database as it is and use that to restore it during your install.

    Pros: Easy to backup/restore, easy to redistribute, very little additional installer code
    Cons: Requires the login to have create database permissions, can't use to deploy updates as it would wipe out user data, has to be run from the DB Server

    2. Detach/Attach
    Pretty much the same as Backup and Restore, attach would additionally require the database files to be in the correct directory (where sql server has permissions to files).

    3. Scripted
    This is my favorite option as it has the most flexibility. On the opposite side it also has the downside of being the most complex and installer code intensive. I personally believe the pros far outweigh the cons on this.

    Pros: Database can be created by admin previous to install, no elevated login rights required, framework can be used to deploy updates, can be run from the client machine on install (as only a connection is needed)
    Cons: Requires much more code to check for errors, table and schema changes, etc.

    -Sean

    Thursday, February 23, 2012 3:18 PM

All replies

  • Hello,

    There are a few different options.

    1. Backup and Restore
    As you have already mentioned, it is possible to take a backup the database as it is and use that to restore it during your install.

    Pros: Easy to backup/restore, easy to redistribute, very little additional installer code
    Cons: Requires the login to have create database permissions, can't use to deploy updates as it would wipe out user data, has to be run from the DB Server

    2. Detach/Attach
    Pretty much the same as Backup and Restore, attach would additionally require the database files to be in the correct directory (where sql server has permissions to files).

    3. Scripted
    This is my favorite option as it has the most flexibility. On the opposite side it also has the downside of being the most complex and installer code intensive. I personally believe the pros far outweigh the cons on this.

    Pros: Database can be created by admin previous to install, no elevated login rights required, framework can be used to deploy updates, can be run from the client machine on install (as only a connection is needed)
    Cons: Requires much more code to check for errors, table and schema changes, etc.

    -Sean

    Thursday, February 23, 2012 3:18 PM
  • Hi aniyahudi,

    In ordinary, we always create a database to support one application (software), because it is more convenient for us to manage the database.

    For backup and restore database, a database backup plan is crucial to protect the business data asset. If there is a data-error problem and the database is unrecoverable, the DBA can use the database backup to recover the database to a consistent state. Moreover, the better database backup strategy will reduce the amount of data loss in case of certain kinds of errors encountered during the course of the daily database activities.

    I would suggest you to read these references:
    http://msdn.microsoft.com/en-us/library/ms175477.aspx
    http://msdn.microsoft.com/en-us/library/bb545450.aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 27, 2012 9:07 AM
    Moderator