Link to home
Start Free TrialLog in
Avatar of indy500fan
indy500fan

asked on

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

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
Avatar of Aneesh
Aneesh
Flag of Canada image

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
Avatar of indy500fan
indy500fan

ASKER

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

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.
you need to run sp_Who2 from master db


USE MAster
GO
Exec sp_Who


and kill those processes
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' ;
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.
ASKER CERTIFIED SOLUTION
Avatar of indy500fan
indy500fan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Closed, 500 points refunded.
Computer101
EE Admin