SQL Database Project - Import an Existing Database
This post is a follow up to this post about a talk I gave at the Boston Code Camp.
One of the questions at the end of my talk was:
How do you deal with multiple, dependent databases, including using symbolic links?
I decided the best way to explain working with dependancies is to do a step by step database import example.
If you would like to skip to the part about dependancies click here.
This tutorial was created with Visual Studio 2013 and SQL Server 2014 Express.
The sample project for this tutorial is on Github here.
For this example I’m going to use two dummy databases, “Database1” and “Database2”.
A view in Database2 is dependent on a table in Database1.
For this example I am importing into Git, and will have the source code on Github here.
I chose to not import logins, permissions, or db settings because it gets complicated and for most cases you won’t need it.
When you choose to import the solution into Git, some files are created for you:
.git directory - Holds the files that store the Git version history
.gitattributes - What kind of line ending should be used?
Note that there were some issues with text=auto setting all line endings to CRLF, I’m not sure if this is still a problem, but if you see this issue then can you can change text=false.
.gitignore - Tells what files to NOT commit, such as files generated during the build process.
Visual Studio created a Git repository for us, but no files have been added.
To add the files to Git, you can use Visual Studio’s Git integration.
Push Changes to server (in this case Github)
Now that you have Database2 imported, you will see that there are some errors:
Go to the Error tab and view the errors:
There are two errors listed, lets resolve one at a time.
The second error is because it is legal to reference the current database name in a view, but not legel in an SQL Database Project:
Once we remove the self-reference the error is resolved:
The other error is a little more involved. The view is referencing another database.
There are two ways to resolve this error.
- Import the database that is being referenced into the solution.
- Add a reference to a .DACPAC (for more info on DACPACs, see this post)
For this example, I am going to do #2 above, because I don’t want to go through the hassle of having to fix any errors with Database1 after importing it.
First we have to create the .DACPAC for Database1
I like to make a folder under the project that holds DACPACs that will be referenced.
Once the DACPAC is created, we need to add a reference to the Database2 project.
Here you have some choices to make, you can either use a database variable to refer to the referenced database (see above), or you can clear out the “Database variable” field and reference the database by name:
For this example I’m going to use the Database variable option so that I can show how to use variables when publishing. For simple database references, I usually clear out the variable and just reference the database by name.
You will also see that I clicked “Suppress Errors”, this will suppress any reference errors if Database1 references another database.
At this point the database is imported and the errors are resolved.
In a future blog post I will cover making changes and publishing those changes to a new or existing database.
Symbolic links allow you to reference another database with a name you give the link.
To publish a database that references another database using a symbolic link, simply set the database variable equal to the name used in the Symbolic Link.