Home > Blockchain >  Local SQL Server database custom file location
Local SQL Server database custom file location

Time:05-01

ASP.NET Core Web App 6 template with Identity Framework creates a default connection string:

Server=(localdb)\\mssqllocaldb;Database=aspnet-WebApplication1_RazorPages_test-53bc9b9d-9d6a-45d4-8429-2a2761773502;Trusted_Connection=True;MultipleActiveResultSets=true

The database files are created in the C:\Users\{MyUser} folder. I want the database to be created in a different location, for e.g. {Project}\DataBase.

I tried to add AttachDbFileName=|DataDirectory|\aspnet-WebApplication1_RazorPages_test-53bc9b9d-9d6a-45d4-8429-2a2761773502.mdf as an explicit path, but that returns an exception:

SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid.).

I also tried AttachDbFileName=C:\aspnet-WebApplication1_RazorPages_test-53bc9b9d-9d6a-45d4-8429-2a2761773502.mdf, as well as few other locations. The current Windows user is Administrator, so there is no issue with permissions.

I noticed the original connection string uses Database instead of Initial Catalog. I have seen in the past.

How can I enforce a custom database file location, preferably within the VS project folder?

CodePudding user response:

You can do this by changing the default settings from the sql server.

Step 1. Right Click on Server and Select "Properties".

Step 2. in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. You can also change default backup location here.

Step 3. Click on "OK" to apply changes.

All new databases will be created to new location unless specified explicitly.

enter image description here

CodePudding user response:

1 Step Make mssql service local system account then restart

then add this connection string to the project

Data Source=./;AttachDbFilename=C:\Users\Bayram Eren\source\repos\WebApplication2\test.mdf;Initial Catalog=test;Integrated Security=True

  • Related