SQL database deployment

[kindofsudden]

I have read many articles here, but haven't quite found the solution. I have a SQLEXPRESS database that is used by my VB.NET application. I have packaged and deployed the application via an MSI file and everything works great except I cannot figure out how to include my database file with the package. I understand there are three general ways to do this (copy the files over manually, custom actions, and SQL scripts). I didn't need anything fancy here, just a quick way to put the DB on the client machine so my app can access it.

I decided copying over the DB manually was the quickest option. I tried putting it in the working directory and in the \DATA directory of the client's SQLEXPRESS install, but my app wouldn't connect. I also tried changing my connection in the project to ".\SQLEXPRESS" instead of "[my_computer_name]\SQLEXPRESS" followed by a rebuild of the deployment project and reinstall on the client machine, but no soup for me. Same issue. I tried changing the "UserInstance" property in the project to "True" but my project would not let me save that action.

So first, am I correct that a manual copy is the quickest and easiest way to get this done? Second, what am I doing wrong?

Thanks ahead of time!

[Reverend Jim]

Before you use the database you have to attach it. You can use the following command

sp_attach_db 'dbname','datafilename','logfilename'

If your database and log files are named D:\SQL\DATA\mydb.mdf and D:\SQL\LOG\mydb_log.ldf then your command looks like

sp_attach_db 'mydb','D:\SQL\DATA\mydb.mdf','D:\SQL\LOG\mydb_log.ldf'

You might want to truncate the log file before you move it off your machine.

[kindofsudden]

Somewhat new to this type of attachment. Where do I put this code?

[Reverend Jim]

You can execute it like any other query string. I usually use ADO. You can run a vbScript file or you can code it in your app (it could detect if the database is not attached and prompt the user for the location). The vbScript code is

DBNAME = "PUBS"
DBFILE = "D:\SQL\DATA\Pubs.mdf"
DBLOG  = "D:\SQL\DATA\Pubs_log.ldf"

set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server};Server=.\SQLEXPRESS;Trusted_Connection=yes;"
con.Execute "sp_attach_db 'PUBS','" & DBNAME & "','" & DBFILE & "','" & DBLOG & "'"
con.Close

and you will notice my slight oopsie on the earlier post (which I will correct). I didn't include the dbname parameter which should go first. This is the name that will appear in SQL server for your database.

[Reverend Jim]

One more note - I just found out that sp_attach_db has been deprecated. The recommended method to attach is shown by the following example

query = "CREATE DATABASE PUBS " & _
        "       ON(NAME='PUBS_Data',FILENAME='D:\SQL\DATA\Pubs.mdf') "     & _
        "   LOG ON(NAME='PUBS_Log' ,FILENAME='D:\SQL\DATA\Pubs_log.ldf') " & _
        "   FOR ATTACH WITH ENABLE_BROKER;"

set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server};Server=.\SQLEXPRESS;Trusted_Connection=yes;"
con.Execute query
con.Close

[kindofsudden]

Okay, I think I am envisioning how this works. I suppose I would set this up to detect whether it exists in the Form.Load event so this code runs on the first setup and never again after that, correct?

[Reverend Jim]

If by on the first setup you mean the first time it is run after setup, then yes. And just so you know, there is no rule that states the db/log files have to be stored in the same folder that SQL uses by default.

[kindofsudden]

Awesome information. Thanks, everyone!