September 24, 2007 at 1:53 am
I have a strange problem with a database. It's physical filename = "E:\someplace\converse.mdf" but it's logical file name is master. The transaction log is simillarly odd. It's physical filename = "E:\someplace\converse.ldf" but it's logical file name is mastlog. It's not right but what has happened? What problems will this cause? I can of course alter the logical filenames but I fear that this database may be woven into the master database. Any thoughts would be much appreciated.
September 24, 2007 at 8:57 am
My guess is that someone took a copy of master at some point, moved it to your database's location and attached it under a different name. No database can be linked into master on 2000 - it's totaly standalone. If you're worried, take a look at the physical file names of the master database and prove to yourself that they're different. Then go ahead and change the logical filenames.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 24, 2007 at 9:11 am
Paul has the best guess as to what happened, but it doesn't really matter. The logical or Physical names have no impact on users querying the server. They're just administrative names and we match them just for ease of use.
I tend to rename the physical files if I move them to a new server and attach them with a new name.
September 24, 2007 at 9:26 am
Thanks for the input. I now believe I know what has happened. It seems likely that the application tables and SPs were installed into the master DB. The mistake was realised and a backup made. The database was then restored to a new database. The database contains sys objects that should only ever be in the master DB so I guess this confirms my suspicions. It has no ill effect when the database is running under SQL 2000 but when I try to attach it to SQL 2005, I get an error stating that "the object already exists in sysnsobjs". My plan (and please point out any flaws in this) is to drop all the system objects in my database that don't belong in a standard database.
September 24, 2007 at 1:39 pm
Very interesting - I'm sure its because the DB used to be master. The whole way of storing database metadata changed in 2005 and I'll bet there are some hard-coded entries put into that table which exist in master. I'll investigate and blog about it later this week 🙂
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply