Monday, 9 July 2012

Opening a SQL Server 2012 database in Visual Studio 2010

After installing SQL Server Express 2012 and using the SQL Server Management Studio to create a database, I found that I couldn't integrate SQL Express 2012 into the Visual Studio Server Explorer in a way that VS listed all my 2012 databases. VS would show databases from an earlier 2008 SQL Server I had installed on my PC but not the 2012 version.

The only way I could show the DB was to add a connection to a database file. To do this I had to:-

1. Right-click the Data Connections node in Server Explorer and choose Add Connection...

2. Make sure the Data Source field was set to SQL Server Client as shown in this pic.:

3. Then in Server name field type in the name of the new SQL Server Express 2012 instance. In the pic. above, the name of my server is SLIQSQL but for a default installation of SQL Server Express, the server name is likely to be SQLEXPRESS (I just chose a different name during the installation process).

4. Then press the Test Connection button. If all is well you should get a Connection Succeeded message.

5. Finally, choose the database you want to add in the Select or enter a database name field and press OK to close the dialog.

The chosen database should now show in the Server Explorer.

Where does SQL Server 2012 store database files by default?

If you're playing around with SQL Server 2012 and you've created a couple of databases, e.g. in SQL Server Management Studio, you may not actually know where the databases are stored as during the whole database creation process you don't get asked to specify a file path.

To find out where your database files are stored do the following:-

1. Open SQL Server Management Studio
2. Right click the server name in the Object Explorer pane on the left and choose Properties.
3. In the Properties dialog, choose Database Settings in the Select a Page pane on the left.

The database file locations are then shown on the right.