opc.three (4/4/2013)
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account?
...
If I were the sysadmin on a production server, then I'd insist on having a login on the host system server. Really this type of one-off maintenance operations at the file system level occur more frequently in the development environment. In this situation they needed the ability to unit test two different releases of the ETL in parallel, which is hopefully not something a production sysadmin would ever have to screw with.
On this particular instance, which they are hosting on a virtual server they provisioned a few weeks back, I happened not to have a local host login. I could have put in a request to have a host login added, but that would have added another day to turn around time. I probably will request a local login now. However, even with that host login, I can still see the benefit of just leveraging xp_cmdshell in a T-SQL script, so I can more easily perform the same set of operations again when needed. It could be done using PowerShell, but I perfer T-SQL, and there is only that one step that involves the file system.
As a development DBA, there are just aspects of my job and routine tasks that would not and should not be performed by a production DBA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho