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
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
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 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
ASKER
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.
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
USE MAster
GO
Exec sp_Who
and kill those processes
ASKER
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' ;
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
@filename2 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\IRLRV2
ASKER
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.
ALTER DATABASE [IRLRV3]
MODIFY FILE ( NAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
NEWNAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
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\
It makes me think my syntax is wrong because the IRLRV2.mdf file does exist.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Closed, 500 points refunded.
Computer101
EE Admin
Computer101
EE Admin
sp_Who2
and kill those process ids those are running on that DB ... you can kill the processID using a 'KILL' statement
KILL ProcessID