Search code examples
sql-serverasp.net-mvcmvs

Moving DataBase from Microsoft SQL folder to App_Data folder (appseting.json)

1. "WebApi": "Data Source=.;Initial Catalog=TaskDB; Integrated Security=true"
   

2. "WebApi": "Server=(localdb)\\mssqllocaldb;AttachDBFilename=%CONTENTROOTPATH%\\App_Data\\TaskDB.mdf;Trusted_Connection=true;MultipleActiveResultSets=true"

I am trying to move my DataBase from main folder of Microsoft SQL to project folder App_Data, but it does not work for some reason. I do not know why maybe my connection string is wrong. So with number 1 is working fine, but it is in main folder of Microsoft SQL, but with number 2 there is something wrong I guess

enter image description here

Solution

  • The files of database are exclusive in hand of SQL Server. You can not move database when it is online. Taking database offline, requires that no one be connected to database.

    First take the database offline then try to move the files. You can take the database offline both using SSMS and query. First line of code kill all active sessions then set database multiple user; but it must be offline before anyone can connect to database so all these code must be executed together.

     ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 GO
 ALTER DATABASE DatabaseName SET MULTI_USER
 GO
 USE master
 GO
 ALTER DATABASE DATABASE_NAME SET OFFLINE

    Be aware that when you move file to another location database could not been brought ONLINE if you do not set new filename before taking the database offline. Use this code for all of files that you want to move them.

    ALTER DATABASE TEST 
    MODIFY FILE (NAME = 'LOGICAL_NAME', FILENAME = 'New_Directort\Filename.mdf')

    After you moved the files then bring online the database with this statement.

    ALTER DATABASE DATABASE_NAME SET ONLINE

    As you can see this action is not a kind, that can be done without plan. Specially with application code. When trying to do it in application code; then all session including application connection will be lost. then you can not continue the progress.