Wednesday, May 03, 2006

Xcopy deployment of databases using SQL Server Express

For one of our new projects we are trying to figure out the easiest way to deploy local databases that will be used by SQL Server Express.

We have several options (there may be others but these are under consideration):
  1. Script the whole thing (including data)
  2. Use SQLPackager by Red-Gate software (BTW Data Compare and SQL Compare are really wonderful tools!)
  3. Xcopy deployment

I prefer the xcopy deployment as this saves us a lot of trouble. Why is it so easy? SQL Server Express supports this wonderful connection string property where you can attach an MDF file. This is a really powerful feature that gives you a lot of flexibility. Do notice that the SQL Native Client is required to support this option.

Server=.\SQLExpress;AttachDbFilename=c:\Data\myDB.mdf;
Database=myDBName;Trusted_Connection=Yes;

Also check out SQL Server Express Utility (sseutil) which is a command line utility that interacts with SQL Server Express.

*EDIT*
Want to know more about xcopy deployment? Check out this link I just found :-( Always check the books online st*pid :-)

No comments: