March 23, 2010 at 4:59 pm
Hey all,
I have a question concerning renaming the physical files of a database. Normally, the way I do this is to detach the database, rename the files from Explorer, and then reattach the database. However, when I tried to do this recently, I received the error "Access is denied". Basically, Windows seems to think the MDF file for the database is in use, even though it is detached. I ran sp_who to see if there were any processes using the DB, but none turned up. I also looked to see if it was showing up as an open file in Computer Management (it wasn't).
I have two questions:
1. Does anyone have any idea as to why this database file is showing up as in use? Any suggestions on methods I could use to get to the bottom of this issue?
2. Is there a better way to rename the physical files of a database than the one I described above? Ultimately, I guess I should get better at specifying the database filenames when I backup or restore a database in the first place, but is there a way of renaming the physical files of a database while it is attached to a SQL instance?
I would appreciate any advice that anyone can offer. Thanks.
March 23, 2010 at 6:01 pm
Are you able to re-attach the file?
I have seen where it was necessary to reattach and then detach again in order to rename.
Another scenario is the file permissions. Verify those permissions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 23, 2010 at 6:45 pm
Hey,
Thanks for your reply. Re-attaching and detaching the database didn't work, but logging in as another user did. So I guess it was a permissioning issue. Strange though, cos I was logged in as the main admin and couldn't do it, but when I logged in as my personal username I was able to. Anyway, thanks again for the suggestions.
March 23, 2010 at 6:58 pm
Another option for renaming a MDF/LDF file is to restore a back and use the WITH MOVE option. This will allow you to give it a new name.
Also, and I haven't tired it, but I don't see why you wouldn't be able to rename the mdf/ldf file with the ALTER DATABASE MODIFY FILE tsql statement.
Fraggle
March 23, 2010 at 7:22 pm
cjohn5552 (3/23/2010)
Hey,Thanks for your reply. Re-attaching and detaching the database didn't work, but logging in as another user did. So I guess it was a permissioning issue. Strange though, cos I was logged in as the main admin and couldn't do it, but when I logged in as my personal username I was able to. Anyway, thanks again for the suggestions.
You're welcome.
Out of curiosity, is the DBOwner the same account you logged in with in order to get it to work?
If so, that would be the reason. It would be worthwhile to change the DB Owner to something different. (It's worked for me in the past.)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 12:06 pm
Hi,
I don't recall for sure, but I think it was. Is the DB owner set by default to the user that was logged in and restored the database?
March 31, 2010 at 12:14 pm
Yes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 12:22 pm
Personally, I make sure that none of our databases have user as owner. As the keeper of the sa password (at the moment I AM the only DBA that knows it on most of our systems that I manage), I make the sa the owner of the databases. This way, when people leave, there is no need to worry when their user account is deleted or locked out.
March 31, 2010 at 12:43 pm
Lynn Pettis (3/31/2010)
Personally, I make sure that none of our databases have user as owner. As the keeper of the sa password (at the moment I AM the only DBA that knows it on most of our systems that I manage), I make the sa the owner of the databases. This way, when people leave, there is no need to worry when their user account is deleted or locked out.
This is the method I prefer as well. I don't like users being the owner of the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 2:53 pm
Hi,
Thanks for all the replies. Database ownership isn't something I had really considered until recently. Is there a system table that lists the owners of all the different databases, so I can check to ensure that they are all owned according to best practices?
March 31, 2010 at 3:20 pm
cjohn5552 (3/31/2010)
Hi,Thanks for all the replies. Database ownership isn't something I had really considered until recently. Is there a system table that lists the owners of all the different databases, so I can check to ensure that they are all owned according to best practices?
This code returns accurately that information.
if charindex('Microsoft SQL Server 2005',@@version) = 0
select name [database], suser_sname(sid) [owner] from master.dbo.sysdatabases
else
select name [database], suser_sname(owner_sid) [owner] from sys.databases
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 3:32 pm
The best way to rename a file is to offline the database, use alter database modify file to amend the file path in sys.master_files, physically rename the file to match, then online the database
---------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply