Like application code, databases should be able to use source control and undergo continuous integration and continuous delivery. This capability is provided by Visual Studio. Here's how to import databases into Visual Studio Database Project.
Prerequisite
- Must select ‘Data Storage and Processing’ while installing workloads during Visual studio installation
- you must have database in SSMS
Create a New Project
In Visual Studio - select Create a new project.
Search for database and select SQL Server Database Project. Click Next.'
Enter the name of the project. The Solution Name defaults to Project Name. click Create
Import your database in Visual studio
Right-click on the project and select Import, then Database.
Input connection info and uncheck “Import referenced logins”. We don't want to accidentally change login rights between UAT and Production. As in both environment, login user has different permissions,
At development machine select True in ‘Trust Server Certificate’. At Production server must install this certification
Click Start
This will import your selected Tables in your Project
A few administration folders and schema folders will appear after the import.
Development Management
You can sync Visual Studio or database changes. it is recommend to use Visual Studio for table manipulation and global searches and updates. When you have functional code, you may prefer to work in the database and sync views, stored procedures, etc.
Pull Changes from Database
- After database development, pull changes into Visual Studio.
- To import modifications into Visual Studio, select Tools, SQL Server, New Schema Comparison.
- Enter your database connection under Select Source.
- Select the project as the target..
In the Results pane, we will get a list of Add, Changed, and Deleted objects after the comparison. The Object Definitions pane displays source code discrepancies. Splitting the window lets you see both and scroll through updated objects.
- Uncheck the box next to the plus symbol to avoid pushing objects to the destination. Expected group highlighting and excluding works.
- To Get updates, click Update to apply project modifications. Confirm the update and your project is updated.