rename physical files?

Is it possible to rename physical MDF and LDF files? I have been searching
the net for a few hours and I thought I got close with an ALTER DB command.
But I got errors. I think that is only for logical names. Anyhow, here is
what I tried:
alter database pre_8live_hc modify file (name = 'restoredb', newname =
'pre_8live_hc')
alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
'pre_8live_hc_log')
This is what I got back:
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'restoredb' does not exist.
Server: Msg 5041, Level 16, State 1, Line 2
MODIFY FILE failed. File 'restoredb_log' does not exist.
The DB name is pre_8live_hc
Filenames are: (logical / physical)
MDF = NTI_hc_data / d:\program files\microsoft sql
server\msssql\data\restoredb.mdf
LDF = NTI_hc_data_log / d:\program files\microsoft sql
server\msssql\data\restoredb_log.mdf
I have seen some stuff about restore with replace and also attachdb, but
could not get definitive answer. And the commands I tried did not work.
Thanks,
Bryan
You can detach the database, move the physical files (and/or rename them)
and then attach the database. If you can't get that to work, show us the
syntax you are using for sp_attach_db and we'll try to see what's wrong.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"IT Department" <it@.ntihc.com> wrote in message
news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
> Is it possible to rename physical MDF and LDF files? I have been
> searching the net for a few hours and I thought I got close with an ALTER
> DB command. But I got errors. I think that is only for logical names.
> Anyhow, here is what I tried:
> alter database pre_8live_hc modify file (name = 'restoredb', newname =
> 'pre_8live_hc')
> alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
> 'pre_8live_hc_log')
> This is what I got back:
> Server: Msg 5041, Level 16, State 1, Line 1
> MODIFY FILE failed. File 'restoredb' does not exist.
> Server: Msg 5041, Level 16, State 1, Line 2
> MODIFY FILE failed. File 'restoredb_log' does not exist.
> The DB name is pre_8live_hc
> Filenames are: (logical / physical)
> MDF = NTI_hc_data / d:\program files\microsoft sql
> server\msssql\data\restoredb.mdf
> LDF = NTI_hc_data_log / d:\program files\microsoft sql
> server\msssql\data\restoredb_log.mdf
>
> I have seen some stuff about restore with replace and also attachdb, but
> could not get definitive answer. And the commands I tried did not work.
> Thanks,
> Bryan
>
|||Hi,
Follow kelens method. What you did is to rename the Logicalfile name. TO
rename the logical database name you have to be in that database.
Thanks
Hari
SQL Server MVP
"IT Department" <it@.ntihc.com> wrote in message
news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
> Is it possible to rename physical MDF and LDF files? I have been
> searching the net for a few hours and I thought I got close with an ALTER
> DB command. But I got errors. I think that is only for logical names.
> Anyhow, here is what I tried:
> alter database pre_8live_hc modify file (name = 'restoredb', newname =
> 'pre_8live_hc')
> alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
> 'pre_8live_hc_log')
> This is what I got back:
> Server: Msg 5041, Level 16, State 1, Line 1
> MODIFY FILE failed. File 'restoredb' does not exist.
> Server: Msg 5041, Level 16, State 1, Line 2
> MODIFY FILE failed. File 'restoredb_log' does not exist.
> The DB name is pre_8live_hc
> Filenames are: (logical / physical)
> MDF = NTI_hc_data / d:\program files\microsoft sql
> server\msssql\data\restoredb.mdf
> LDF = NTI_hc_data_log / d:\program files\microsoft sql
> server\msssql\data\restoredb_log.mdf
>
> I have seen some stuff about restore with replace and also attachdb, but
> could not get definitive answer. And the commands I tried did not work.
> Thanks,
> Bryan
>
|||Thanks guys. The sp_attach_db worked. I will have to admit I didn't try
just renaming the physical files. For some reason I thought I had tried
that in the past and I kep getting errors saying the file didn't exist, but
now that I think back, I was trying to delete one of two transaction logs
and it wouldn't attach back without both logs.
Thanks a bunch!
Bryan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OeLusr5yEHA.3408@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Follow kelens method. What you did is to rename the Logicalfile name. TO
> rename the logical database name you have to be in that database.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "IT Department" <it@.ntihc.com> wrote in message
> news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
>

Rename A Field Using SQL Statement?

Is there a way that i can rename some fields in a table using SQL from my
.NET program? The customer send sthem in as BMT Number and I need
BMT_Number, etc.
Thanks
dbWhat about looking in the BOL, there is an example for tenaming
columns:
Renaming a column
The following example renames the TerritoryID column in the
SalesTerritory table to TerrID.
Copy Code
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Removing foreign keys

Hi everyone,

I've been using VS.NET to design and implement my SQL databases. This has been simple and effective.

I've deployed the database onto the production database server and it's been performing without a hitch for some time now.

My problem is that I now need to remove a foreign key relationship on the production database as I have had a relationship between two tables but now not wanting the relationship since the data that had the relationship is now not wanting to be compulsory. I've had a look in the system.foreignkey (I think) table but I can't make head or tail of it.

Long story short, how can I find and remove the relationship from Query Analyser / Enterprise Manager ? I know how to do this in VS.NET diagram (just delete the relationship) but when you open the production database in VS.NET the diagram is no longer there.

Any help will be appreciated!!

Thanks!
AndrewFixed - was browsing around in VS.NET on the server, right clicked on the table in question, and found the relationship tab, deleted relationship.

Thanks!
Andrew