Monday, October 29, 2012

SSDT: Importing an Existing Database

To Import From an Existing Database
Import Database into your project by right-clicking the project name or selecting the Project Menu and selecting the Import -> Database option. Note that you can also import from SQL Scripts or an existing dacpac file. One of those may be necessary if you don't have direct access to your source server.
clip_image001
You'll then be asked to choose your project name, location, folder structure, and what to import. You can choose the defaults if you want. If you have an intricate permissions structure, you may want to import the permissions. If your logins, users, and permissions tend to vary by environment, you may want a different way to handle those. That will be covered in the "Permissions" section.
You'll have several options for folder structure. I'd choose the recommended structure unless you have a reason to choose something else. That will structure all of your objects under their appropriate schema, by object type. If you have a relatively simple database model, you may choose to structure your project by schema alone, object type alone, or just put everything in the root of the project. This only affects your project file structure, not your database structure.
Once you're satisfied with your settings, click the "Start" button to begin populating your project.
clip_image002
 
Importing From a Dacpac File
SSDT supports importing from a dacpac file. This can be useful if you're working on a project for a database to which you do not have direct access in order to import the schema directly. These files also can be used as external database references if you work in an environment where multiple databases interact.
In order to create a dacpac file, you have a couple of options.
You can generate a dacpac file from a SQL Server database using SSMS 2012. Right-click the database you wish to use. Select Tasks, then "Extract Data-tier Application…".
clip_image003
Set the file location and any other options you want to use.
clip_image004
Click next. Verify the settings. Click Next again. The system will now generate a dacpac file in the location you specified.
Note that choosing the "Export Data-tier Application" will create a bacpac file. This is used to backup all schema and data in a database so it can be published to an Azure database. You cannot use a bacpac file to import schema into your project.
 
Generating a dacpac using SQLPackage.exe
You can always use the SQLPackage command line to extract the DB Schema into a dacpac file. For a complete list of parameters, see http://msdn.microsoft.com/en-us/library/hh550080%28v=VS.103%29.aspx.
For a quick extraction from a trusted server, you can run something like the following:
Code Snippet
  1. Sqlpackage.exe /a:extract /ssn:localhost /sdn:Adventureworks2008 /tf:Adventureworks2008.dacpac
This command uses an "Action" to Extract the file, with the SourceServerName of localhost, a SourceDatabaseName of Adventureworks2008, and a TargetFile of Adventureworks2008.dacpac




















No comments:

Post a Comment