Xcopy Deployment (SQL Server Express)
Xcopy is a simplified way to deploy your applications and SQL Server Express databases. Xcopy deployment enables you to copy a new application (.exe) and the database (.mdf) file to another computer, or to a different location on the same computer. No additional configuration is required.
To distribute the application to users, you send just the .exe and .mdf files. Each user can save the files locally and just double-click the .exe to start the application. If your application uses replication, you should not use Xcopy deployment.
To use Xcopy deployment, SQL Server Express must be installed on the target computer and must have an instance running. For more information, see User Instances for Non-Administrators.
How Do I Use Xcopy Deployment?
When an application first establishes a connection from a running instance of SQL Server Express, SQL Server Express will automatically attach an .mdf file. When the user closes the application, SQL Server Express detaches the .mdf file from the instance. The Xcopy feature causes the .mdf file to be fully portable. You can copy and move the file and, at the same time, run multiple copies of the file on the same instance.
Logical Database Naming
When SQL Server Express attaches a database, it automatically generates a logical name for the database, unless the connection string in your application specifies a logical name. This logical name must be unique. SQL Server Express will use the drive letter, directory name, and database file name as the logical name. For example, if your .mdf file is in the C:\Program Files\My Application\Data\MainData.mdf path, SQL Server Express will assign a logical database name that exactly matches the path:C:\Program Files\My Application\Data\MainData.mdf.
Note
If the length of the full path and file name exceeds 127 characters, the logical database name is shortened and prefixed with a GUID. This guarantees a unique logical name.
Log File Creation and Naming
When you deploy your application, you should include in the Xcopy only the application files and the .mdf file. You should not include the log file (.ldf). SQL Server Express will automatically create a new log file when it attaches the database. SQL Server Express will name the log file database_name_log.ldf. If a file that has the same name already exists in the same directory as the .mdf file, the existing file is used.
Limitations when Using Xcopy Deployment
When you use Xcopy deployment with your application, some SQL Server Express features might not function as expected. The following list describes limitations that might occur.
Any Transact-SQL code that contains logical database names will not work.
Because the logical name of the database depends on the file location, which will change with each Xcopy, any code that specifies a logical database name will fail.
Replication scenarios do not work.
Replication requires a persistent logical database name. If your application uses replication, you should not use Xcopy deployment.
Xcopy Deployment for Administrators on a SQL Server Express Instance
To make your application work with the Xcopy deployment feature of SQL Server Express, you must make sure that the connection string you use in your application contains the appropriate parameters:
Use the data source parameter, but change the computer name to either a period (.) or (local). You must also specify the name of the instance, unless you are sure that SQL Server Express will always be installed on an unnamed instance.
Use the initial catalog or database parameter, but do not set the parameter to a value.
Add the AttachDBFileName parameter and set it to the name and path of the .mdf file. Attachdbfilename is a SqlClient connection string option that enables attaching databases at runtime and autogenerates database name. The DataDirectory keyword lets you specify the relative path of a database file. Attachdbfilenamealso helps with database portability. For more information about Attachdbfilename, see the Visual Studio 2005 documentation.
The following connection string will attach the MyDb.mdf database file, which is in the same folder as the application executable, to the SQL Server Express instance running on the local computer.
@"Data Source='.\SQLExpress'; Initial Catalog=; Integrated
Security=true; AttachDBFileName='" |DataDirectory| +
@"\MyDb.mdf'"
Xcopy Deployment for Non-Administrators on a SQL Server Express Instance
To deploy an application for users who do not have administrative user rights on the instance of SQL Server Express, you must specify the following:
The parameters in the connection string for Xcopy deployment.
The user instance parameter.
For more information, see User Instances for Non-Administrators.