So last week I needed to rename some physical database files so that I didn’t have any naming conflicts when I restored some other databases. I had about 10 databases that I needed to rename both the physical database files as well as the databases, and I really didn’t want to detach each database, then go find the files, rename them and put it all back together one at a time.
With a little work, I was able to beat the SQL Server into submission and using just T/SQL (and xp_cmdshell which I had to enable for this specific task) I was able to detach the databases rename the physical files and then attach the databases back. The trick here is that SQL Server changes the permissions on the physical database files when you detach the database so that only the person who detaches the database can touch the physical files. The way that I was able to get around that was via the xp_cmdshell proxy account. I changed the proxy account to use my domain account, but this still didn’t quite do the trick. The reason that it didn’t is because as a member of the sysadmin fixed server role I bypass the proxy account and anything which I do using xp_cmdshell uses the SQL Service account by default.
To get around this using of the SQL Service account I had to use the execute as to impersonate a lower level account. In this case I chose the guest account as the account to use when I executed xp_cmdshell. The code that ended up getting run against the database instance.
[sql]exec sp_detach_db ‘prod_db’
go
execute as user = ‘guest’
go
exec xp_cmdshell ‘rename W:Dataprod_db.mdf test_db.mdf’
exec xp_cmdshell ‘rename T:TLogsprod_db.LDF test_db.ldf’
go
revert
go
exec sp_attach_db ‘test_db’, ‘w:datatest_db.mdf’, ‘t:tlogstest_db.ldf’
go[/sql]
Don’t you worry I didn’t write out all those code by hand. I used a SQL query to generate the xp_cmdshell and the bulk of the sp_attach_db code. That SQL was…
[sql]select ‘exec xp_cmdshell ”rename ‘ + filename + ‘ ‘ +
replace(substring(filename, len(filename)-charindex(”, reverse(filename))+2, len(filename)), ‘prod_’, ‘test_’)
+ ””, ‘, ”’ + replace(filename, ‘prod_’, ‘test_’) + ”” from prod_db.dbo.sysfiles[/sql]
Hopefully you never need to go through this exercise, but in case you do hopefully this will come in handy.
Denny
