I still remember many of the tasks I performed as a DBA for the first time. One of the first requests to come my way was to rename a database. Simple enough, or so I thought. I did a dump, then a restore, renaming both the database and the files at the same time. Bing, bang, bung and I was done. I dropped an email to the developer that had made the request and explained that the task was done as well as the steps involved. At the time I thought it was important to communicate such details but have come to learn that a simple reply often works best.
The developer responded back to my detailed explanation with a simple question: why not just use sp_renamedb? Why all the extra work?
I checked with one of the other still remaining DBA’s and asked their opinion. Should I have used the stored procedure? Was the extra work necessary? The answer was surprising: it depends. I responded back to the developer and tried to explain that, to me, it seemed to make more sense to take the extra steps. The developer responded that I was wasting my time. We agreed to disagree at that point and I made a mental note to not bother giving more details than necessary on any future requests.
What’s The Problem?
Well, quite simply, sp_renamedb does not rename the database data files. To me, that is a problem, and not just because of my OCD-like compulsion to have the database data files contain a matching database name. If we have a database named <dbname>, then the initial data files would typically be dbname_data.mdf and dbname_log.ldf. Why would that matter to anyone?
It matters a lot to someone like me. For example, if I ever have a log drive fill up I like to look at the files on the log drive, see the name of the file, and immediately know which database is causing the issue. Is there an easier way to get that same information? Absolutely. Now go back and read the second sentence of this paragraph again and this time focus on the following words; I like. Yeah, it’s my preference to work this way, I find it to be more efficient to RDP to the server, examine the drive, then fire up SSMS on the server and start drilling.
Because sp_renamedb does not rename the db files this would prevent me from quickly knowing which database is causing the issue. To be more exact, it would force me to learn new habits in order to quickly know which database is causing the issue. Oh sure, I suppose someone will tell me how great Powershell can solve this problem for me, and I suppose I could also tell you how I could configure Operations Manager to not only troubleshoot for me but to take action for me as well. And all of that is fine, really. But it is all overhead as well; overhead that is avoided if you adhere to a naming convention right from the start.
Think of it this way: if a server administrator responds to a disk being full, how nice would it be for them to also understand the naming convention and to assist by notifying the correct group (as well as your team)? No need for anyone else to know about DMV’s, or Powershell, or even Operations Manager, the naming convention just makes life a little easier.
That’s Not The Reason For This Blog Post
Nope, it is not. The reason for this post is to tell you that SQL 2008 gives us a brand new way to rename a database using T-SQL. The sp_renamedb procedure has been deprecated. It will still work, but you should start using the ALTER DATABASE command instead. So, to change a database name quickly you are to use:
Simple right? Well, no, not really. You see, this syntax only changes the logical filename. If you want to change the physical filename you need to do one of two things. One is to do a detach, rename the files, and reattach the database. The other?
Well, that would be the good old fashioned backup and restore, like I did all those many years ago. Yessir, everything old is new again.
See, you cannot change the physical name of the data and log files while they are in use. Therefore, your options are limited in how you want to go about getting that done. So, if you use SSMS, sp_renamedb, or ALTER DATABASE you can rename the database just fine. If you want to rename the actual physical files then you need to go the extra mile. If you ask me, it is worth the distance.
Tom,
I did the same thing except I could not figure out the WITH MOVE syntax so I used the DTS wizard to move it to a different DB.
JM
oh? so you can get a little OCD about the file names as well?
No, just didn’t know about sp_renamedb. That is pretty anal about the file names.:)
I would stay away from attaching and detaching a database. You can easily lose login information that way. Instead use:
ALTER DATABASE
MODIFY FILE (NAME=, FILENAME=);
Then take the database offline, move the underlying files to the new path and new name, and bring it back online. This preserves all the login info.
would i lose login info if i kept everything on the same instance? i was always under the impression that same server restores did not require me to take any extra measures when it comes to logins. i cannot imagine that a detach and attach would be have differently than a same server restore.
now, detach and then reattach to a new server would certainly cause an issue with logins and permissions, no doubt. it is the same with a restore as well, you need to take extra steps to preserve the logins and permissions.
There’s a third way to change the physical filenames.
ALTER DATABASE newdbname
MODIFY FILE (NAME=’newdbname_data’, FILENAME=’D:\Somewhere\newdbname.mdf’)
— and same for the log
GO
Then take the DB offline, rename the files from a file manager, bring the DB back online.
Keep in mind there could be the issue of 3 part naming of the database in the stored procedures and other objects using the old database name.
By doing detach and attach, if you’ve got anything else configured, like FILESTREAM or cross database ownership chaining, you lose that configuration. Better to use the ALTER which allows you to do the move without losing the config info.
i wholeheartedly agree with you on the first method. simple and names are under your control. wont confuse you or any new guy who comes along after you without knowing about what you did. saves a lot
of headaches when you go back to that later (say a year after).
***
Check http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx
Use select spid from master..sysprocesses where dbid = db_id(‘Works’) and spid @@spid
kill all spids
and then rename from Microsoft SQL Server Management Studio
it does not renames the .mdf file