My development environment is C#, SQL Server 2014 LocalDB, SQL Server 2012 Express, Windows 10, Visual Studio 2015.
When users of my application need to move their localDB (.mdf) file to another place, another computer (LocalDB server), detaching from computer A and attaching to computer B and then, we can run BACKUP database command successfully.
However, in case users mistakenly detached or users changed their mind to use continuously in computer A, my application has to be able to re-attach the detached LocalDB database file (.mdf) to the same computer (same LocalDB server).
When I run BACKUP DATABASE command after my application re-attached the database file to same computer successfully, error message shows as,
Unable to open physical file, The process cannot access the dbfile because the dbfile is in use by another process
BACKUP DATABASE terminated abnormally
So, I entered Microsoft Server Management Studio and can see 2 dbfile with specific name as first is greendb.mdf (only name), second is c:\users\kay\appdata\greendb.mdf (with full path).
I think the c:\users\kay\appdata\greendb.mdf (with full path) is created when the database is detached. And when I click it through security-login-kay-user mapping, unlike other databases show their permissions inside, the detached database with full path doesn't show their permissions and show error message like,
Unable to cast 'System.DBNull' object to 'System.String' (Microsoft.SqlServer.Smo)
It seems Microsoft LocalDB Server still recognizes the detached database with full path and is confused with newly attached database (only name without full path).
Any excellent ideas will be highly appreciated !
Thank you so much !
In detaching localDB,
we have to run ALTER DATABASE ROLLBACK IMMEDIATE command first to terminate all the incomplete transactions.
Just to explain easily, Before we close a Restaurant, we have to announce to the customers in the Restaurant, 'This Restaurant will be closed very soon, please complete your eating and get outside before closing of Restaurant'
If you're needed to re-attach the localDB to same computer(same localDB Server),
Some activities like these have to be avoided to prevent the ghost(bug?).
1) Trial to open the localDB in code programmatically
2) It seems counting with the name of detached localDB also reminds the existence of localDB to the localDB Server.(SELECT COUNT dbname command in master database)
Strange thing which has to be fixed as a bug is,
if we detach a localDB from master DB, I think it has to be not able to open the detached localDB in code programmatically. However, code like SqlConnection.Open(); runs and pass by without any exception(error) and immediately the fullpath ghost is created.
It seems the name of detached localDB is deleted on master DB but the Server connects the detached localDB through the physical path in the provided connectionstring.
And to decide some localDB is needed to attached or to check it's detached or not, I've developed my own solution(simple code) to do this.
Hope my experience helps someone else.