December 7, 2007 at 7:40 am
I am a university professor and dozens of times per semester receive and attach SQL Server 2005 DB files (.mdf and .ldf) from students. I copy their files to my hard drive, attach the files and examine them and sometimes run .Net applications against the files. When I finish with their DBs I detach them but keep the copy on my local drive for reference. The students are instructed to only use Windows Authentication when creating their DBs.
In Windows 2005 I must change the file ownership of the DB in order to create or view DB diagrams. Since this is a very important evaluation tool I have been changing ownership to sa routinely.
My problem is that once I make this change I can no longer move or copy the DB .mdf/.ldf files. If I have not needed to view a diagram I can move them easily. I need to archive this semester's materials but cannot move/copy many of the DB files. My Roxio CD program reports that the files are in use by another program. (At this point the DBs have been detached, SQL Server is stopped, Management Studio is not running, and in fact the computer has been restarted with the DB Engine set to start in manual mode.)
It also seems that once I've made these changes to the file, I can no longer attach them (logged in using Windows Authentication).
1. Does anyone know of a quick and easy way to release whatever file system lock SQL Server has placed on the files so I can move/copy them under Windows?
2. Can anyone point me to a technique that will allow me to view the Diagrams without locking up the files?
December 7, 2007 at 8:37 am
Not sure about moving the diagrams. You should be able to see them as sa without issue. Maybe check the rights inside the db to see if something is weird.
You can get some utilities here (sysinternals): http://www.microsoft.com/technet/sysinternals/fileanddiskutilities.mspx?wt.svl=featured
I htink process explorer or psFile to see where the lock is being held. If SQL is shut down, there shouldn't be anything holding the file. Be sure you have the right file and there aren't two named in different folders that are confusing you.
BTW, it's cool you're using SQL Server in college. Is this Express? May I ask which college?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 7, 2007 at 10:08 am
Thanks much for the response. I'll try the link.
I teach at the University of Central Florida in Orlando. You can check my course page at http://systems.bus.ucf.edu/lwest. Visit the course page and then the ISM 4212 (undergraduate database) page. If you really don't have anything to do you can see the Project link there.
We are using SQL Server Developers' Edition. The students do a complete DB design and implementation (within a narrow set of organizational functions) and then build a VB.Net application to accomplish the functions. All SQL is embedded in stored procedures (and some UDFs though these and triggers are taught and tested but not required in the app) and they build connections, commands, parameters, data adapters, data readers, data sets, etc., in code. They also do one embedded SQL Server Reporting Services report each using the same DB and viewed through a ReportViewer control.
My undergraduate eCommerce class does the same project but with an ASP.Net interface and also builds a web service project.
My biggest challenge is to have them do professional quality development when they do not have a central server on which to manage a single project for a dispersed group. The students and I get used to slinging DB and code objects around the Internet with wild abandon and sometimes run into some huge version control issues :w00t:.
I think I may have solved the diagram ownership problem. I made my windows identity a sysadmin on the SQL Server and on the next project I attached I did not have to change file ownership to view the diagrams and this has been a universal requirement in the past. I'm still evaluating it but will see if I can move it afterwards.
Thanks again.
December 7, 2007 at 11:18 am
Thanks for the update and glad it appears to be working.
And kudos again for using SQL Server at UCF!
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply