Xcopy Deployment (SQL Server Express)

  • Article

Microsoft SQL Server 2005 Express Edition (SQL Server Express) supports Xcopy deployment of your application and the SQL Server Express database, significantly simplifying distribution of applications that use SQL Server Express databases.

What is Xcopy Deployment?

Xcopy deployment is the ability to copy your application and the database (.mdf) file to another computer, or to a different location on the same computer, without requiring any additional configuration. SQL Server Express must already be installed on the target computer and has an instance running to use Xcopy deployment. For more information about instances, see Using Named Instances.

With Xcopy, when you distribute your application to users, you can send just the .exe and .mdf files. Each user can place these files in a folder and double-click the .exe to start using the application.

How does Xcopy Deployment Work?

SQL Server Express can automatically attach an .mdf file to a running instance of SQL Server Express when an application first establishes a connection. When the user closes or exits the application, SQL Server Express detaches the .mdf file from the instance. The feature results in the .mdf file being fully portable: you can copy and move the file, and simultaneously 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, ensuring a unique logical name.

Log File Creation and Naming

When you deploy your application, you should 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 with 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 may arise:

  • 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 certain that SQL Server Express will always be installed on an unnamed instance. For more information about named and unnamed instances, see Using Named Instances.
  • 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 permits attaching databases at runtime and autogenerates database name. The DataDirectory keyword lets you specify the relative path to a database file. Attachdbfilename also 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

In addition to all the parameters specified in the connection string for Xcopy deployment for administrators on an instance, you must specify the user instance parameter to deploy an application for users who do not have administrative privileges on the instance of SQL Server Express. For more information about user instances, see User Instances for Non-Administrators.

See Also

Other Resources

Distributing SQL Server Express

Help and Information

Getting SQL Server 2005 Assistance