In the previous post we saw how to create a new project using SSDT. In this entry we’ll see how to import an existing database into the project. Start by right clicking on the project (not the solution) and pick Import, Database.
The Import Database is similar to the one from the 2010 database projects, but simplified. Use the New Connection button to setup a connection to your database (here I picked Adventure Works 2012). Target Project is disabled, since it’s in the context of the current project.
Import settings can be left at their defaults. The one thing to note is the Folder structure drop down. I personally prefer the default of Schema\Object Type. You can also pick None, which will put all the SQL files in the root of the project. I wouldn’t recommend this option, as it will quickly get difficult to find the files you need to edit. You can also organize by just Schema, or just Object Type. If you are a hard core DBA you might find Object Type more comfortable, since it’s closer to the Object Explorer in SSMS. As I said though, my experience has been Schema\Object Type is the easiest to work with.
When it’s done just click finish, and you’ll see the new structure in the Solution Explorer. Each folder at the top level represents a Schema, or database level object such as Database Triggers.
In the image below, you can see I expanded two of the schemas, HumanResources and Person. Under these are folders for all of the present object types.
Note that the HumanResources schema has a folder for Stored Procedures, while Person does not. This is simply because in the database the Person schema has no stored procedures. If you want to add a stored procedure to the Person schema, you’ll want to add a folder to the Person structure and name it Stored Procedures. This isn’t required, you can put the SQL file anywhere you want, but if you mimic the existing organization structure you’ll make it much easier to maintain and expand the SSDT project as you move forward.
Lets expand a branch to see all the files.
Finally! We’ve drilled down to the lowest level and can see the individual files that are needed to make up the project.
In the next installment we’ll look at altering some of the database settings. Over the next few weeks we’ll be looking at deployment tools, database snapshots, and how to edit the various file types, and some of the enhancements there, especially around the table editor.
Arcane Code 
Is there any way to restore a backup from inside VS 2012? I have a backup of a DB from another server and would like to restore it to my DB on my machine locally.
Hi Craig,
You have a couple of options. Assuming your are using SSDT, you could simply hit the SQL menu, Transact-SQL Editor, New Query. Then you can enter standard T-SQL commands to restore a database, just like you might in SQL Server Mangement Studio.
If you want menu choices, there isn’t one to restore from in Visual Studio, you’ll need to use SQL Server Management Studio.
If you need to import a database, as opposed to doing a restore, there is a way to get the Schemas in place with Visual Studio using SSDT. In the SQL menu, do a Schema Compare (this will be the subject of an upcoming blog post). With it you can copy the structures from one database to another. Note, this only covers the structures, not the data itself. Right now SSDT doesn’t have an option for data comparisons.
If you still have VS2010, or 2008 for that matter, and have the VS Database Projects, there is an option in it to do data comparisons. This is a great feature, and will let you get the data from one database in sync with another.
Note that this really isn’t meant to do full database backups and restore, so it’s not the fastest in the world. It’s more designed for situations like having a “gold standard” test database, one that has all the proper conditions for performing unit tests, and getting your actual test database back in sync after running said tests.
By far though the simplest method is to just use SQL Server Mangement Studio. When I do development I generally have both open.
Hope this gives you some ideas, stay tuned each Tue and Thr over the next few weeks will be blog posts on Visual Studio SSDT in VS2012. I’ll also be speaking on the subject at devLink in Chattanooga, in just a few weeks.
Robert
how we create a database in vs 2012