I upgraded my Visual Studio 2008 Professional edition to the more robust Visual Studio Team System (VSTS) Database Edition. My goal is to have a better toolset for managing database versioning than SSMS, and to support the inclusion of both database and .NET projects in the same solution.
After installing, I attempted to create a database project using the SQL Server 2005 template, both with and without the wizard walk-through. This resulted in a general error message, “Failed to create database”. There was no additional information in the Event Log and no log file was created in the project file.
To solve this, I used the SQL Server 2005 profiler tool. Visual Studio requires a local install of SQL Server that it uses to validate schema. When a project is created, Visual Studio attempts to create a database that matches the project name if one does not already exist in the designated local instance. By examing the profiler output from the database creation attempt, I was able to see that Visual Studio was attempting to create physical database files in directories that were no longer valid. I had moved all of my database files from Z:\Data\MSSQL to Z:\Mssql\Data to match the convention of a client. However, after deleting the first directory, I had not changed the default data directory setting in SQL Server to the new location. When I created databases using the SSMS interface, SQL Server allows me to manually replace the invalid setting with a valid one. Visual Studio, however, relies on this setting to determine file placement. When I corrected the setting SQL Server’s database options, the Visual Studio project creation completed successfully.