Renaming a Database Physical and logical file names

I have a situation where I need to change a database's name both the logical
name, AND the physical filenames.
I have found several methods that change the Logical name only but keep the
old filenames which will not work in my situation because there is a high
probability a new database might be created that will use the old name.
Thanks,
RonHi Ron,
You can take the database offline by detaching the database. Rename the
physical files and you use sp_attachdb command to attach the new files.
Regards
Shri.DBA
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I need to change a database's name both the
logical
> name, AND the physical filenames.
> I have found several methods that change the Logical name only but keep
the
> old filenames which will not work in my situation because there is a high
> probability a new database might be created that will use the old name.
> Thanks,
> Ron
>|||You can change the Logical name with ALTER DATABASE but not the physical
unless it is tempdb. But I believe you can detach it, change the name and
reattach it again specifying the new name.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I need to change a database's name both the
> logical name, AND the physical filenames.
> I have found several methods that change the Logical name only but keep
> the old filenames which will not work in my situation because there is a
> high probability a new database might be created that will use the old
> name.
> Thanks,
> Ron
>|||I tried this code I wrote...but I get the error:
Run-time error '55555':
Microsoft ODBC SQL Server Driver SQL Server To connect to this server you
must use SQL Server Management Studio or SQL Server Management Objects.
-- CODE:
Option Explicit
Public strSQLServerLogin As String 'In ModCommon
Public strSQLServerPassword As String 'In ModCommon
Public strSQLServerHostName As String 'In ModCommon
Public strADOConnTypeSQL As String 'In ModCommon
Public strSQL As String 'In ModCommon
Public strDBToCopy As String
Public strNewDBName As String
Private Sub Command1_Click()
strSQLServerHostName = "HOST"
strSQLServerLogin = "sa"
strSQLServerPassword = "PASS"
strADOConnTypeSQL = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=" & strSQLServerLogin & ";Password=" &
strSQLServerPassword & ";Data Source=" & strSQLServerHostName & ";Database="
Dim strCurrentDatabase As String
Dim strNewDatabase As String
strCurrentDatabase = Form1.CurrentDB.Text
strNewDatabase = Form1.NewDB.Text
Call RenameSQLDatabase(strCurrentDatabase, strNewDatabase)
End Sub
Public Sub RenameSQLDatabase(strCurrentDatabase As String, strNewDatabase As
String)
Dim oSQL As Object
Dim oSQLDB As Object
Dim strMDFfilePath As String
Dim strMDFfileName As String
Dim strLOGfile As String
Dim strSQL As String
Dim iCnt As Integer
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
' Kill any existing connections to the Database that will be copied
strSQL = "SELECT spid, DB_NAME(dbid) AS dbname FROM
master.dbo.sysprocesses WHERE DB_NAME(dbid)='" & strCurrentDatabase & "'"
oConn.Open strADOConnTypeSQL
iCnt = 0
With oRs
.Open strSQL, oConn, adOpenStatic, adLockOptimistic
If Not .EOF Then
oSQL.KillProcess .Fields("spid").Value
iCnt = iCnt + 1
End If
.Close
End With
oConn.Close
Set oRs = Nothing
Set oConn = Nothing
' Use SQLDMO to Attach, Copy & rename new DB, and reattach target and
source DB
Set oSQL = CreateObject("SQLDMO.SQLServer")
Set oSQLDB = CreateObject("SQLDMO.Database")
oSQL.Connect strSQLServerHostName, strSQLServerLogin,
strSQLServerPassword
Set oSQLDB = oSQL.Databases(strCurrentDatabase, "dbo")
strMDFfilePath = oSQLDB.PrimaryFilePath
strMDFfileName = _
Trim(oSQLDB.FileGroups.Item(1).DBFiles.Item(1).PhysicalName)
strLOGfile = Trim(oSQLDB.TransactionLog.LogFiles(1).PhysicalName)
Set oSQLDB = Nothing
' Detach DB
oSQL.DetachDB (strDBToCopy)
'Copy database files to new names
FileCopy strMDFfileName, strMDFfilePath & strNewDatabase & ".mdf"
FileCopy strLOGfile, strMDFfilePath & strNewDatabase & "_log.ldf"
'Attach original database and new database
oSQL.AttachDB strCurrentDatabase, "[" & strMDFfileName & "],[" &
strLOGfile & "]"
oSQL.AttachDB strNewDatabase, "[" & strMDFfilePath & strNewDatabase &
".mdf]" & ",[" & strMDFfilePath & strNewDatabase & "_log.ldf]"
Set oSQLDB = oSQL.Databases(strNewDatabase, "dbo")
Debug.Print "Database Created"
End Sub

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Os6S9mLrFHA.464@.TK2MSFTNGP15.phx.gbl...
> You can change the Logical name with ALTER DATABASE but not the physical
> unless it is tempdb. But I believe you can detach it, change the name and
> reattach it again specifying the new name.
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
>|||
I also tried this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spRenameDatabase]
@.SourceDB varchar(200),
@.BackupPath varchar(2000),
@.DestinationDB varchar(200),
@.DataPath varchar(2000)
AS
Declare @.BackupFile varchar(2000)
SET @.BackupFile = @.BackupPath + @.SourceDB + '.dat'
Declare @.Datafile varchar(200)
SET @.Datafile = @.Datapath + @.DestinationDB + '.mdf'
Declare @.LogName varchar(200)
SET @.LogName = @.SourceDB + '_log'
Declare @.Logfile varchar(200)
SET @.Logfile = @.Datapath + @.DestinationDB + '_log.ldf'
BACKUP DATABASE @.SourceDB
TO DISK = @.BackupFile
RESTORE FILELISTONLY
FROM DISK = @.BackupFile
RESTORE DATABASE @.DestinationDB
FROM DISK = @.BackupFile
WITH MOVE @.SourceDB TO @.DataFile,
MOVE @.LogName TO @.Logfile
IF @.@.ERROR <> 0
RETURN 1
ELSE
RETURN 0
ERROR::::
Msg 3234, Level 16, State 2, Procedure spRenameDatabase, Line 31
Logical file 'RSHTestRename_2' is not part of database 'RenamedDatabase'.
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure spRenameDatabase, Line 31
RESTORE DATABASE is terminating abnormally.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I need to change a database's name both the
> logical name, AND the physical filenames.
> I have found several methods that change the Logical name only but keep
> the old filenames which will not work in my situation because there is a
> high probability a new database might be created that will use the old
> name.
> Thanks,
> Ron
>|||Use EM...
1. Detach your database
2. Rename your physical files
3. Re-'Attach' your database by navigating to your data file (MDF) and
selecting it
4. Change the 'Current File(s) Location' to reflect the new physical
names
5. Change the 'Attach as' to reflect you new logical name|||I would love to but this is a dynamic script that has to be accessible to
users in remote locations.
"MySQLServer" <naka55n@.hotmail.com> wrote in message
news:1125342469.302763.179680@.g49g2000cwa.googlegroups.com...
> Use EM...
> 1. Detach your database
> 2. Rename your physical files
> 3. Re-'Attach' your database by navigating to your data file (MDF) and
> selecting it
> 4. Change the 'Current File(s) Location' to reflect the new physical
> names
> 5. Change the 'Attach as' to reflect you new logical name
>|||Try this then:
sp_detach_db 'saiko'
GO
xp_cmdshell 'rename c:\mg\mssql\saiko1_Data.MDF saiko_Data.MDF'
GO
xp_cmdshell 'rename c:\mg\mssql\saiko1_Log.LDF saiko_Log.LDF'
GO
sp_attach_db @.dbname = 'saiko1',
@.filename1='c:\mg\mssql\saiko_Data.MDF',
@.filename2='c:\mg\mssql\saiko_Log.LDF'
GO|||I keep getting a "The system cannot find the file specified" Error...even
though I am looking right at the files in the server's directory. I copied
and pasted the path and it is Exactly where the file is.
When I run this as a query on the server this path should match up with the
Path that appears in the Properties panel under Files correct? In other
words this is the path on the sever, not my local box right?
Thanks for the help...this looks like the best solution of all.
Ron
"MySQLServer" <naka55n@.hotmail.com> wrote in message
news:1125345464.950289.161550@.g44g2000cwa.googlegroups.com...
> Try this then:
> sp_detach_db 'saiko'
> GO
> xp_cmdshell 'rename c:\mg\mssql\saiko1_Data.MDF saiko_Data.MDF'
> GO
> xp_cmdshell 'rename c:\mg\mssql\saiko1_Log.LDF saiko_Log.LDF'
> GO
> sp_attach_db @.dbname = 'saiko1',
> @.filename1='c:\mg\mssql\saiko_Data.MDF',
> @.filename2='c:\mg\mssql\saiko_Log.LDF'
> GO
>|||do this:
xp_cmdshell 'dir c:\mg\mssql\*.*'
do you see your datafiles?
Make sure the paths you are specifying are relative to the location on
the server.
You can also be sure be looking in sysfiles
select * from sysfiles
Let us know how you make out.

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...
>