Problems with 'locked' MDF File

Hi Folks,

When I create a Database and try to copy the MDF file, I get a message to say that the Database is locked or in use by another user.

Seems like maybe I am not doing a 'Save' operation - although I can't find one ?

If I shut down the PC and restart it, then the MDF File is freed up, although when I try to open it, there is something wrong with it.

I'd appreciate any suggestions.

Thanks a lot.

Barry

The database is in use by another user, it's being used by SQL Server. SQL Server locks the database file while it is attached to the server, which can cause issues when trying to do file copies. You need to detach the database from the server before trying to copy or move the file. You also need to consider copying/moving the log file (.ldf) with the databases, SQL Server uses both of these files. If the database has been correctly closed and detached, you usually don't need to move the log wih the data.

If you plan to copy your database a lot, you can set the AutoClose property to True for the database, this results in the database being closed when there are no connections for the database. Closing a database is different that detaching it, but it will allow you to make a copy of the file. In SQL Express, databases created using the CREATE DATABASE command are already set with AutoClose=True, but if you create the database in management studio, the AutoClose property will be False and you will need to set it manually by accessing the database properties.

Mike

|||

Mike,

Thanks a lot for what looks like a perfect answer.

I'll be able to try it later on today.

I also appreciate the speed of your response.

Thanks again.

Regards

Barry