Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution Automate Database Build Part Three: Create a Database Publish Profile
IntroductionIn Visual Studio 2012, the old Data Dude projects have been replaced by SQL Server Data Tools (SSDT), which encompasses a list of improvements to database development in Visual Studio that was formerly known by the codename “Juneau”. When SSDT was first released there were several issues with compatibility levels, but has improved greatly since the December update.
I’m writing a blog on database deployment (currently still in draft form), and I need a database to show my examples with. This required me to download AdventureWorks 2012 and the corresponding solution for Visual Studio 2012. After some fruitless Googling, I surmised that the Visual Studio sln was not available to download. So I turned to Visual Studio 2012 to extract the schema and create a solution. So below in this example I am going to show you how to extract the AdventureWorks 2012 solution for Visual Studio 2012 either from a live database or a DACPAC. It was seriously easy to do, but there are a few options that you should consider when extracting a real live production database:
The Import Wizard offers four options for a folder structure:
- None. The reason that a new project starts without a folder structure, is because it gives the team an opportunity to define their own structure. Unfortunately, this means also opens up the opportunity for structure-less heap style projects, and I have seen projects imported from previous versions just lose their structure. You’d think that database developers would appreciate the need for structure in their solutions.
- Schema stores all objects connected with a schema, in a corresponding folder. Where other schemas are created, these are all stored in their own folder structures, following the convention of storing all similarly named objects in their corresponding sub-folder structures, within their schemas.
- Object Type imports all objects by Object Type, placing them in a folder structure. This structure follows the SQL Server database folder structure, as displayed through SQL Server Management Studio.
- Schema\ Object Type. imports all objects in similar fashion to the previous data-dude folder-structure for Visual Studio. This is my personal preference, however it does require the most amount of effort to maintain.
1. Extract via Connection To Live DatabaseFirst you need to create a new solution in Visual Studio 2012.
Naming the solution after the database might be a little myopic if you plan to add more database solutions later, but it doesn’t really matter here. Once this is created click on the database project and go to Import>Database.
From here you can select your databse from the drop down box. If you have not connected to the instance before then you need to add a new connection.
Here you select the database server, your connection properties, and select your database and click OK.
Now we come to the Import Settings. It is worth noting that, unlike the previous Data Dude projects, selecting a folder-structure for the Visual Studio 2012 SSDT project, is only required for the familiarity, readability, and maintainability of the solution. The structure has no bearing on the deployment, and does not influence anything in the database structure.
Once you’ve selected your folder structure, you’re ready to import. Depending on the size and complexity of your solution, it shouldn’t take long. Once finished click OK.
Browse your solution and you’ll see your project imported in the folder structure you requested that you will be able to deploy.
2. Extract via DACPACIf you don’t have connection directly to the SQL Server Instance, you will have to extract the DACPAC from the databases. I’ve written a post extracting dacpacs via PowerShell (1) but as we only have the one to extract I’ll just run the cmdline (it’s faster than the Wizard):
"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Extract /tf:"C:\Users\Administrator\Documents\SQL Server Management Studio\DAC Packages\AdventureWorks2012_Baseline.dacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks2012;Data Source=ALTEREDGIRATINA"
The paramters we choose to extract a DACPAC are straightforward in this case, but might be more complex, depending on your SQL setup. Read up on SQLPaxkage in greater detail on the MSDN page.
- /action:Extract creates a database snapshot (in the form of a .dacpac file) from a live database in an on-premise instance of SQL Server or a Windows Azure SQL Database instance. An Extract action can create a .dacpac file that contains just the schema of the source database, or the schema and user table data.
- /tf Specifies a disk file path where the .dacpac file will be written.
- /sourceconnectionstring Specifies a valid SQL Server/Azure connection string to the source database. If this parameter is specified it shall be used exclusively of all other source parameters.
The choices here are far more straightforward: select the dacpac you extracted, choose your Import Settings and hit “start”.