Renaming a Database - the databasename.mdf file that is...


I am creating a utility that works with SQLExpress, and while Express doesn't have an export feature, I would like to be able to allow them to rename the database, so that they can more or less archive the info in that database locally.  Then, when the app resumes, it will create a fresh instance of the database.

Does anyone havethe T-SQL that will allow me to do this?  If I try to do it simply by going through file manager, I get the error message that: "Cannot rename IRLRV: It is being used by another person or program.  Close any programs that might be using this file and try again."

Thanks in advance!
Best Regards,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:

and kill those process ids those are running on that DB ...  you can kill the processID using a 'KILL' statement

KILL ProcessID
indy500fanAuthor Commented:
Hmm...So if I automate,

I need to run this sp_Who2 command,

Then interate through each SPID, killing on the SPID, then rename the file?

Is there not an easier way like no locks and rename?

I'm trying to automate this process.


indy500fanAuthor Commented:
I noticed that when I ran it manually I get:

Msg 6104, Level 16, State 1, Line 3
Cannot use KILL to kill your own process.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Aneesh RetnakaranDatabase AdministratorCommented:
you need to run sp_Who2 from master db

USE MAster
Exec sp_Who

and kill those processes
indy500fanAuthor Commented:
I think what I am actually looking for is:

sp_detach_db @dbname = 'IRLRV'

Rename the two files in the location:
Then, in a SQL Query:

sp_attach_db @dbname = 'IRLRV2',
    @filename1 = N'c:\Program Files\Microsoft SQL Server
    @filename2 = N'c:\Program Files\Microsoft SQL Server
        \MSSQL.1\MSSQL\Data\IRLRV2_log.ldf' ;
indy500fanAuthor Commented:
I think I found an even better method, but I need some syntax help:

MODIFY FILE ( NAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV2.mdf',
NEWNAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV3.mdf' )

When I run this, i get the following error:

Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\IRLRV2.mdf' does not exist.  

It makes me think my syntax is wrong because the IRLRV2.mdf file does exist.
indy500fanAuthor Commented:
Nope, my "New" method won't work.

 To rename the physical files you either have to do a detach/rename/attach or a Backup/Restore and change the file names in the Restore Options.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Closed, 500 points refunded.
EE Admin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.