How to treat your MDF and LDF files

Have you ever set a database to read only? If so, you probably did this by using SQL Server Management Studio or by by executing the regarding ALTER DATABASE command.

Here’s another method that I had to investigate recently: I colleague of mine with some limited knowledge of SQL Server didn’t discover the ALTER DATABASE statement so far. But he knew how to detach and attach a database, because this is what he does all the time in order to copy databases from one computer to another. One day, when he wanted to prevent modifications to one of his databases, he decided to protect the MDF- and LDF- files of these database. Very straightforward he detached the database, set the MDF- and LDF-file to read only mode (by using Windows Explorer) and attached the database after completing this. Voila: SQL Server does not complain at all (I was very surprised about this) and as he expected, the database was displayed as read only in the Object Explorer of SSMS.

As I said: I was very surprised, since I didn’t expect this method would work. Smart SQL Server! But then the trouble began…

Very shortly, after a system reboot, SQL Server started showing the database in question as “suspect”. What happened? I don’t know, but I was able to reproduce the behavior with SQL Server 2008 on Windows Server 2008 R2 every time I repeated the following steps:

• Create a database
• Detach the database
• Set the MDF- and LDF-file to read only
• Attach the database again
• Restart the computer

So, I think you should be careful with any modifications to MDF- and LDF-files outside SQL Server. This not only seems to be true for data itself but also for these files’ attributes. You should always treat MDF- and LDF files as kind of SQL Server’s exclusive property and never touch them!

Just another point to add here: If you try setting a database with read only MDF- or LDF files to read write again by executing ALTER DATABASE, you’ll get an error like this:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\SqlData\User\db2.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\SqlData\User\db2_log.LDF". Operating system error 5: "5(Access is denied.)".
Msg 945, Level 14, State 2, Line 1
Database 'db2' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

But that’s as expected, I’d say.