How SQL Express XCopy Deployment Works

Here is a link to a posting on how SQL Express Xcopy Deployment works.

One thing I would add is to show what the connection string would look like.  This answers the question of how does the application know where to find the database file ? The answer is actually quite simple - just as with an MDB database file, when you are using the file-based approach to accessing SQL 2005 Express you include a path to the physical file in the connection string for the new AttachDBFileName parameter:

Dim ConnStr as string = "Data Source=.\SQLExpress; Initial Catalog=; Integrated Security=true; AttachDBFileName=" & Application.StartupPath & "\MyDb.mdf "

Connecting with this connection string causes the file MyDb.mdf to be automatically attached to the SQLExpress instance.  When the application ends, it will automatically be detached and therefore not locked and available for copying.

For example, if your .mdf file is located at "C:\Program Files\My Application\MyDb.mdf", then SQL Server Express will assign a logical database name that exactly matches the path:

C:\Program Files\My Application\MyDb.mdf

and what you'll see in the Server Explorer is

ServerName\sqlexpress.C:\Program Files\My Application\MyDb.mdf.dbo