This post will wrap up my introduction to SQL Server Data Tools (SSDT). In my first post I wrote about my using SSDT to set up a developer sandbox of a SQL database. My second post covered using SSDT to build a prototype database using LocalDB instead of a SQL instance. I added a third post to clear up some facts I didn’t cover correctly, about LocalDB. Today I’m going to show what the LocalDB looks like, as well as a little more on those .dacpac files created when you take a snapshot of your project.

When you create your first SQL database project SSDT will start SQL but not as a service. You can verify this by opening Task Manager and looking at the running processes. You should see a process sqlservr.exe running under your name. If you’re also running a SQL instance you’ll see a second sqlservr.exe process running under whatever the startup account is. Notice that the location of the executable is C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn.

Sqlservr.exe will also create a new set of system databases for your new project. You’ll find these at C:\Users\your name\AppData\Local\Microsoft\Microsoft SQL Server LocalDB\Instances. You’ll see an instance for every SQL Database project you create.  Confirm this by querying sysfiles. Open that location and you’ll also see the error logs and blackbox traces you’d normally see in SQL instances. Open either to see how your instance is behaving.

Open the location where you saved your SSDT project. You should see a folder called  Sandbox. Inside you’ll see a .mdf and .ldf file for your database. You can detach it and reattach it, or backup and restore it, to another server when you’re ready to move it.

There’s a few things about the snapshots I’d like to mention. There’s other ways to use then instead of just with Schema Compare. You can pass it the latest .dacpac file from your project to another developer who can create a new project based on your snapshot. You can open .dacpacs created in one version of Visual Studio in another; 2012 to 2010 for example. And you can also open it to see what it contains.

A .dacpac file is like a compressed folder. If you open it you can extract the files to a new directory. Inside you’ll see some .xml files with configuration information; database settings for example. You’ll also see one model.sql file that has a create object for all objects in your database. There’s no INSERT statements, though.

Finally, the SSDT team announced an update last Friday. There’s some new stuff there, like opening VS 2012 and only creating one instance on LocalDB instead of one for each project. Check it out on their blog. And Google/Bing for SSDT, there’s a lot of good information out there.