September 1, 2008 at 11:12 pm
Comments posted to this topic are about the item Renaming a Database
September 2, 2008 at 2:57 am
I have used the alter database ... with rollback immediate extensively, it works very well.
However, there is a gotcha with set single_user. I started off using the set single_user command. Some jobs I have developed have a a shrink database command after the step that requires exclusive use. There have been times when an error, or a change of mind occurs and we can't kick the systmem out of the single_user mode resulting in delays. I find using set restricted_user instead is more flexible.
September 2, 2008 at 3:34 am
Pehaps a nice feature would be database name abstraction, similar to how DNS / IP works? This way a nice ID can be assigned to a database, and a name (or names) could be mapped to that ID. That could then allow legacy database names to still work, while new names be used at the same time. I know a lot of people would be up in arms about old names not being cleaned away but then in a practical sence it would help in situations where you dont have time to check and test everything. You may even have compiled code which works perfectly, but the source code is lost or unchangeable for whatever reason.
September 2, 2008 at 4:46 am
Andy,
Good article! Dev uses this in our shop when then need to work with an app and test it against multiple DB's, sometimes it's easier for them to change the DB name vs. the connection strings all over the place (for fear they will miss one)
Mark
September 2, 2008 at 6:29 am
Andy, congratulations for your article, a simple topic yet full of gotchas. Don't forget to add in your list to check for the logins, some of them may have the default database assigned to the renamed database.
September 2, 2008 at 7:22 am
When changing a database name, it's also good practise to change db's logical and physical file names.
September 2, 2008 at 7:33 am
David PROVOST (9/2/2008)
When changing a database name, it's also good practise to change db's logical and physical file names.
Very good point. This is helpful especially if you have restore scripts used to test backups that dynamically reference file names based on the original database name.
September 2, 2008 at 9:26 am
I bumped into the problem of the physical filenames not changing with the Rename operation.
The reason I did a rename was to put "_OLD" on the end of a database name, so that I could create a new one with the original name and restore into it. I could not create the new one because it wanted to use the same physical filenames.
September 2, 2008 at 9:28 am
cy (9/2/2008)
David PROVOST (9/2/2008)
When changing a database name, it's also good practise to change db's logical and physical file names.Very good point. This is helpful especially if you have restore scripts used to test backups that dynamically reference file names based on the original database name.
In this case, and I was thinking the same, it might be better to detach, rename, reattach.
ATBCharles Kincaid
September 2, 2008 at 11:14 am
Thank you all for the comments, some nice additional tips posted!
September 3, 2008 at 9:58 am
If you decide to detach rename and reattach instead - watch out for this issue that bit us when we ran it with SQL Agent job steps:
http://support.microsoft.com/kb/922804
Who ever does the detach must do the reattach since it strips out all the security on the physical files except for whoever ran the detach (nice for security, but not nice for D.R.) - Hint - don't use operating cmd in the SQL agent job steps, changes security context to SQL Agent or Service Account so that any other login cannot attach it unless they have service account login/password. Peachy.
Just a heads up.
Mary
Mary Myers
September 3, 2008 at 12:33 pm
Andy,
Congratulations on the article. I was just wondering about how to get all users off the database. Is changing the database options to Read Only something that would be good to use? It gives you the option of closing all connections to the database. Any pitfalls to using this approach?
Thanks,
Elizabeth Cirello
September 3, 2008 at 4:34 pm
Inconvenient I agree, but if you can find/are an operating system admin, you can take control of the file and assign whatever priviledges you like to the detached file. This could be scripted using the commands TAKEOWN and CACLS.
September 3, 2008 at 11:50 pm
Usually I do this.
Detach
Renamed the physical files(data and log)
Attach
September 4, 2008 at 2:34 pm
Elizabeth, changing to read only doesn't kick them out of the db so its of limited help. Usually the only time I switch to RO mode is when I know there will be no changes to data and want to totally avoid locking overhead.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply