[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2008-01-29
Medium Priority
Last Modified: 2008-03-06

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,
Question by:indy500fan
  • 5
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20768414

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

KILL ProcessID

Author Comment

ID: 20768555
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.



Author Comment

ID: 20768969
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.
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20769162
you need to run sp_Who2 from master db

USE MAster
Exec sp_Who

and kill those processes

Author Comment

ID: 20769800
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' ;

Author Comment

ID: 20770378
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.

Accepted Solution

indy500fan earned 0 total points
ID: 20772699
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.

Expert Comment

ID: 20859013
Closed, 500 points refunded.
EE Admin

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question