We help IT Professionals succeed at work.

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

Medium Priority
2,544 Views
Last Modified: 2008-03-06
Friends,

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,
Eric
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Run  

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

KILL ProcessID

Author

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.

Thanks,
Eric

Author

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.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
you need to run sp_Who2 from master db


USE MAster
GO
Exec sp_Who


and kill those processes

Author

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
        \MSSQL.1\MSSQL\Data\IRLRV2.mdf',
    @filename2 = N'c:\Program Files\Microsoft SQL Server
        \MSSQL.1\MSSQL\Data\IRLRV2_log.ldf' ;

Author

Commented:
I think I found an even better method, but I need some syntax help:

ALTER DATABASE [IRLRV3]
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.
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.
Closed, 500 points refunded.
Computer101
EE Admin

Explore More ContentExplore courses, solutions, and other research materials related to this topic.