SQL Server Data Tools in Visual Studio 2012–Importing a Database

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.

image

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.

SNAGHTML35019c45

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.

image

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.

image

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.

About these ads

4 Responses to “SQL Server Data Tools in Visual Studio 2012–Importing a Database”

  1. craigsn Says:

    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.

  2. arcanecode Says:

    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

  3. SQL Server Data Tools in Visual Studio 2012–Snapshots « Arcane Code Says:

    […] notice, the snapshot is created as a dacpac file. If you happened to notice in the earlier post on creating an SSDT project, in addition to importing from an existing database, we also have the option to import from a […]

  4. puspa Says:

    how we create a database in vs 2012


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: