Friday, October 29, 2010

Blog Mirror on

Two weeks ago Frank Kalis invited me to syndicate my blog on the InsideSql platform that he’s driving for some years now. I gratefully accepted, since I find myself in very good company there.

I will also keep this blog active and just mirror my posts to You may want to check out blog entries on Some interesting stuff going on there. The majority of people uses German for their blog posts, so you should be somewhat familiar with that language. I decided to stick to English. From now on you will find duplicates of my blog posts here and there.


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.