After finishing, we can see the tables, views and stored procedures in your project as shown.
Finally, we have successfully created SQL server Database Project. Now we can publish and create new dataset from solution itself using this project.
Now, I will show how to publish and create new database in SQL server from this SQL server Database Project.
Publish to create new Database in SQL server
We can easily create new database directly from visual studio using the SQL Sever Database Project with same schema.
Let us proceed.
Right click on the project and choose the publish option as shown.
Provide Connection
Server name, Authentication Type, credentials. If we want to publish as new database, then choose default database or choose specific database to publish.
You can new database and publish SQL server database as shown.
Let’s explore advanced options with more rules and options.
Options are,
Deployment behaviors
- Deploy database properties.
- Always re-create database.
- Block incremental deployment if data loss might occurs.
- Execute deployment script in single-user mode.
- Backup up database before deployment.
- Do not user alter assembly statements to update CLR types.
Additionally, there are several advanced deployment options like: compare using target collation, disable and reenable DDL triggers, do not alter change data capture objects, do not alter replicated objects, drop statistics not source, include composite objects, include transactional scripts, populate files o fileGroups likewise several options as shown.
Then, click ok in advanced options and publish the database.
Now, your database is created in SQL server, you can login to SQL server and check as shown.
Finally, we created a database from Visual Studio, SQL server Database project successfully.
Conclusion
In this article, I have shown how to create SQL server database project in Visual Studio and import the sql database. We can import development database and finally publish into UAT, production or another server from visual studio itself with this project template. Additionally, we have explored some advanced option during imports and publishing of the database. There are some additional useful options or use cases we have with this project like to compare and identify the differences in two databases (development and production), then we can generate updated script or update directly from the project. I will explain more about comparing and updating in the next article.