How to use Umbraco with user instance SQL database:
When I'm testing something or starting new small project with Umbraco I prefer using the SQL Server database, it's more tested than VistaDB, and if solution grows you can easily upgrade to the full SQL Server. Also I can move my solution from the laptop to a developer machine.
1. Create empty userinstance database (.mdf file)
1.1 Open VS, Create new website project called EmptyDBForUmbraco
1.2 Right click on App_Data in solution explorer -> Add new item
1.3 Choose SQL Server Database, name it UmbracoDB.mdf
1.4 Find database in Windows explorer (in VS.NET Professional you can rightclick App_Data -> Open in Windows Explorer)
1.5 Close Visual Studio -> must be closed otherwise datadase is in use
(This empty UmbracoDB.mdf can be used multiple times)
You can download created file here our.umbraco.org/.../empty-sql-express-db-for-umbraco
2. Use it with Umbraco
2.1 Create App_Data folder in your Umbraco instalation
2.2 Copy UmbracoDB.mdf into new App_Data folder (do not copy UmbracoDB_log.LDF it will be created automatically)
2.3 In step 2/5 in the Umbraco installation choose custom and use this connection string (note DatabaseName must be unique for each installation on same machine:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UmbracoDB.mdf;Integrated Security=True;User Instance=True;Database=UmbracoDB
3. Complete installation
Notes
* If you want move the installed Umbraco, add a file named App_offline.htm into your Umbraco installation, it causes the database to detach and you can then backup the whole Umbraco installation
* If you move the site, delete the database log file, it should be created when the computer attaches to the database
* SQL Express must be installed
Pros:
* You do not need create a database user
* You can backup the website with its database
* Easily move the website between computers
* Easy upgradable to the full SQL Server
* Some hosting companies allow user instances - easy deployment
Cons:
* Is harder to connect to a userinstance database using SQL Management Studio, etc.