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.