Moving SQL database files using T/SQL only.

Published On: 2010-12-16By:

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.

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

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…

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

Hopefully you never need to go through this exercise, but in case you do hopefully this will come in handy.

Denny

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via