[WiX-users] Create database by attaching mdf and ldf file

[Peter Björkman]

Hi everyone



I have been trying to create a database by attaching a pair of mdf/ldf files. I can't get it to work properly so I hope someone has an easy solution for this.



I have been using a SqlString element with a call to sp_attach_db but I am getting problems. First I used the mdf file as keypath for the component but as I don't want any data to be destroyed at uninstall the component for the mdf and ldf files must be permanent and never be overwritten. Then I got the problem that the database was not attached if the files already existed on the machine.



What I want to do is



At Install:

Copy mdf and ldf files but not overwrite existing files.

Attach the database.



At uninstall

Detach the database.



At major upgrade.

Do nothing.



Any Ideas?



Can the SqlDatabase element with SqlFileSpec and SqlLogFileSpec be used in some way to attach a pair of mdf/ldf files?



Best regards



Peter Björkman





<Component Id="DatabaseAttachment" Guid="....">

<Sql:SqlString SqlDb="MasterDB"

Id="AttachScript"

ContinueOnError="no"

ExecuteOnInstall="yes"

SQL="sp_attach_db @dbname=N'[DATABASE_NAME]', @filename1=N'[INSTALLDIR]Database\MultiAccess.mdf', @filename2=N'[INSTALLDIR]Database\MultiAccess.ldf'"

Sequence="1"

/>

<Sql:SqlString SqlDb="MasterDB"

Id="DetachScript"

ContinueOnError="yes"

ExecuteOnUninstall="yes"

SQL=" exec sp_detach_db @dbname='[DATABASE_NAME]', @skipchecks='true';"

Sequence="1"

/>

</Component>

[Yan Sklyarenko]

I have recently managed to implement something similar for my installation. You can find the whole story here: http://ysdevlog.blogspot.com/2009/01/attach-detach-database-during.html

Also this mail archive contains a number of relative examples, for instance, in this thread: http://n2.nabble.com/Attach-a-database-td712370.html#a712373

Hope this helps.

-- Yan

-----Original Message-----
From: Peter Björkman [mailto:***@aptus.se]
Sent: Wednesday, January 21, 2009 5:48 PM
To: General discussion for Windows Installer XML toolset.
Subject: [WiX-users] Create database by attaching mdf and ldf file

Hi everyone



I have been trying to create a database by attaching a pair of mdf/ldf files. I can't get it to work properly so I hope someone has an easy solution for this.



I have been using a SqlString element with a call to sp_attach_db but I am getting problems. First I used the mdf file as keypath for the component but as I don't want any data to be destroyed at uninstall the component for the mdf and ldf files must be permanent and never be overwritten. Then I got the problem that the database was not attached if the files already existed on the machine.



What I want to do is



At Install:

Copy mdf and ldf files but not overwrite existing files.

Attach the database.



At uninstall

Detach the database.



At major upgrade.

Do nothing.



Any Ideas?



Can the SqlDatabase element with SqlFileSpec and SqlLogFileSpec be used in some way to attach a pair of mdf/ldf files?



Best regards



Peter Björkman





<Component Id="DatabaseAttachment" Guid="....">

<Sql:SqlString SqlDb="MasterDB"

Id="AttachScript"

ContinueOnError="no"

ExecuteOnInstall="yes"

SQL="sp_attach_db @dbname=N'[DATABASE_NAME]', @filename1=N'[INSTALLDIR]Database\MultiAccess.mdf', @filename2=N'[INSTALLDIR]Database\MultiAccess.ldf'"

Sequence="1"

/>

<Sql:SqlString SqlDb="MasterDB"

Id="DetachScript"

ContinueOnError="yes"

ExecuteOnUninstall="yes"

SQL=" exec sp_detach_db @dbname='[DATABASE_NAME]', @skipchecks='true';"

Sequence="1"

/>

</Component>