Removing a server: logical

Showing posts with label logical. Show all posts

Wednesday, March 28, 2012

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.

Monday, March 26, 2012

rename of logical file name

Hi,
If I will rename the logical file name in production will it create any issues.(sql server 2000).

I am getting the problem while restore the production database to other server. So,that I am planning to rename the logical name. The restoration is automated job for all the databases. That restoration script is working fine execpt for 3 databases because the logical names are different for these databases
Any advice.
Regards
Bharat

Quote:

Originally Posted by bharadwaj

Hi,
If I will rename the logical file name in production will it create any issues.(sql server 2000).

can you explain your problem little bit more.|||Hi,
Thanks 4 ur response.

I have taken the backups from production and copy to other server ,then I will execute this restoration automated script for all the databases.
My script is given below.
--
--
set @.logical_data_name =@.db_name + '_' + 'data'
set @.logial_log_name =@.db_name + '_' + 'log'
--
--
RESTORE DATABASE @.db_name
FROM DISK = @.backup_filename
with replace,
MOVE @.logical_data_name to @.phsical_data_name
MOVE @.logial_log_name TO @.physical_ldf_filename

So,my script supports if the logicalname and physical names are same.
ex:: logical names are abc_data,abc_log
phsyical names are abc_data.mdf,abc_log.ldf

But for 3 databases say dbname is test.
logical names are test1_data,test1_log
phsyical names TEST_DATA.MDF,TEST_LOG.LDF

so, I am planning to rename the logical names to(test_data,test_log) in production. My question is will it creates any issues.
Thanks
Bharat

Friday, March 23, 2012

rename logical name**

Hi
How can I rename the name of logical name of my Database?
is it possible in SQL server 2000?
any help would be greatly thankful
RM
Alter DATABASE in the BOL
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful
|||Hi,
Did you mean to rename the logical name of MDF and LDF. Then use
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME =
'NEW_MDF_NAME')
do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful
|||Hi,
with ALTER DATABASE statement you can change logical name of database.
ALTER DATABASE database_name
MODIFY NAME = new_database_name
Ana
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful
sql

rename logical name**

Hi
How can I rename the name of logical name of my Database?
is it possible in SQL server 2000?
any help would be greatly thankfulRM
Alter DATABASE in the BOL
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful|||Hi,
Did you mean to rename the logical name of MDF and LDF. Then use
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME ='NEW_MDF_NAME')
do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
--
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful|||Hi,
with ALTER DATABASE statement you can change logical name of database.
ALTER DATABASE database_name
MODIFY NAME = new_database_name
Ana
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful

rename logical name**

Hi
How can I rename the name of logical name of my Database?
is it possible in SQL server 2000?
any help would be greatly thankfulRM
Alter DATABASE in the BOL
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful|||Hi,
Did you mean to rename the logical name of MDF and LDF. Then use
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME =
'NEW_MDF_NAME')
do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful|||Hi,
with ALTER DATABASE statement you can change logical name of database.
ALTER DATABASE database_name
MODIFY NAME = new_database_name
Ana
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapjtykahqligo@.msnews.microsoft.com...
> Hi
> How can I rename the name of logical name of my Database?
> is it possible in SQL server 2000?
> any help would be greatly thankful

Rename logical name of a datafile

Hi all:
How i can rename the logical name of a datafile?
Best regards
FernandoAssuming SQL Server 2000, you can do it using ALTER DATABASE (See Books Onli
ne for details).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:B717FD0B-E98A-4709-9BB7-3A4CC0DE0FC4@.microsoft.com...
> Hi all:
> How i can rename the logical name of a datafile?
> Best regards
> Fernando|||if you want to change the logical name and you're on sql2k...
alter database dbname modify file(name=oldname, newname=newlogicalname)
if you're on sql7, you could use this script.
-- Rename Logical name
-- Created by OJ Date: 05-24-00
--_--
Set quoted_identifier off set ansi_nulls on
Set nocount on
-- Allow system files to be updated
Exec sp_configure 'allow updates', 1
Reconfigure with Override
go
Declare @.dbname varchar(30),
@.newDataName varchar(64),
@.newLogName varchar(64)
Select @.dbname=rtrim(ltrim(db_name())_)
Select @.newDataName=@.dbname + '_Dat',
@.newLogName=@.dbname + '_Log'
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
-- Update dataName
Update sysfiles1
set name=@.newDataName
where fileid=1
-- Update LogName
Update sysfiles1
set name=@.newLogName
where fileid=2
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
set nocount off
go
-oj
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:B717FD0B-E98A-4709-9BB7-3A4CC0DE0FC4@.microsoft.com...
> Hi all:
> How i can rename the logical name of a datafile?
> Best regards
> Fernando

Rename logical name of a datafile

Hi all:
How i can rename the logical name of a datafile?
Best regards
Fernando
Assuming SQL Server 2000, you can do it using ALTER DATABASE (See Books Online for details).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:B717FD0B-E98A-4709-9BB7-3A4CC0DE0FC4@.microsoft.com...
> Hi all:
> How i can rename the logical name of a datafile?
> Best regards
> Fernando
|||if you want to change the logical name and you're on sql2k...
alter database dbname modify file(name=oldname, newname=newlogicalname)
if you're on sql7, you could use this script.
-- Rename Logical name
-- Created by OJ Date: 05-24-00
--X--
Set quoted_identifier off set ansi_nulls on
Set nocount on
-- Allow system files to be updated
Exec sp_configure 'allow updates', 1
Reconfigure with Override
go
Declare @.dbname varchar(30),
@.newDataName varchar(64),
@.newLogName varchar(64)
Select @.dbname=rtrim(ltrim(db_name())X)
Select @.newDataName=@.dbname + '_Dat',
@.newLogName=@.dbname + '_Log'
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
-- Update dataName
Update sysfiles1
set name=@.newDataName
where fileid=1
-- Update LogName
Update sysfiles1
set name=@.newLogName
where fileid=2
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
set nocount off
go
-oj
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:B717FD0B-E98A-4709-9BB7-3A4CC0DE0FC4@.microsoft.com...
> Hi all:
> How i can rename the logical name of a datafile?
> Best regards
> Fernando

Rename logical name of a datafile

Hi all:
How i can rename the logical name of a datafile?
Best regards
FernandoAssuming SQL Server 2000, you can do it using ALTER DATABASE (See Books Online for details).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:B717FD0B-E98A-4709-9BB7-3A4CC0DE0FC4@.microsoft.com...
> Hi all:
> How i can rename the logical name of a datafile?
> Best regards
> Fernando|||if you want to change the logical name and you're on sql2k...
alter database dbname modify file(name=oldname, newname=newlogicalname)
if you're on sql7, you could use this script.
-- Rename Logical name
-- Created by OJ Date: 05-24-00
----
Set quoted_identifier off set ansi_nulls on
Set nocount on
-- Allow system files to be updated
Exec sp_configure 'allow updates', 1
Reconfigure with Override
go
Declare @.dbname varchar(30),
@.newDataName varchar(64),
@.newLogName varchar(64)
Select @.dbname=rtrim(ltrim(db_name()))
Select @.newDataName=@.dbname + '_Dat',
@.newLogName=@.dbname + '_Log'
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The OLD logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
-- Update dataName
Update sysfiles1
set name=@.newDataName
where fileid=1
-- Update LogName
Update sysfiles1
set name=@.newLogName
where fileid=2
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=1
Select 'The NEW logical name for ' + rtrim(filename) + ' is: ' + name from
sysfiles1 where fileid=2
set nocount off
go
-oj
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:B717FD0B-E98A-4709-9BB7-3A4CC0DE0FC4@.microsoft.com...
> Hi all:
> How i can rename the logical name of a datafile?
> Best regards
> Fernandosql

rename logical file

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||No. It goes quickly.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> > rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> > affect anything on the server?
> >
> >
>|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files
> > named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > to
> > rename it as CC_Data and CC_Log to make it consistent. Would this
> > renaming
> > affect anything on the server?
> >
> >
>|||Does changing the logical name also change the physical (file) name? If not,
is there a method of doing that too?
"Tom Moreau" wrote:
> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physical
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
> > No. It goes quickly.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> > Hi,
> >
> > Would it affect anything / any process on the production server?
> >
> > "Tom Moreau" wrote:
> >
> > > You can change the logical file names, using ALTER DATABASE, without
> > > taking
> > > the DB's offline.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > > Hi,
> > >
> > > I have 3 databases running on Production server. All the databases have
> > > names like AA, BB and CC with their Logical Data Files and Log files
> > > named
> > > as
> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > > to
> > > rename it as CC_Data and CC_Log to make it consistent. Would this
> > > renaming
> > > affect anything on the server?
> > >
> > >
> >
> >
>|||> Does changing the logical name also change the physical (file) name?
No.
> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...
> Does changing the logical name also change the physical (file) name? If not,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
>> The logical filename is just an easy way to refer to it when using ALTER
>> DATABASE. They are unique to the DB - not the server - so you can have a
>> number of DB's with the same logical filenames, but with different physical
>> filenames. This is convenient is a scenario where you have one DB per
>> client and what all of your DB maintenance scripts to do the same thing to
>> each customer DB.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
>> Hi Tom,
>> Can you please explain to me what exactly is the purpose of a logical
>> Filename? When we have more than one databases on one SQL Server, can we
>> have
>> the same logical name for all the databases? Thanks in advance.
>> "Tom Moreau" wrote:
>> > No. It goes quickly.
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> >
>> >
>> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
>> > Hi,
>> >
>> > Would it affect anything / any process on the production server?
>> >
>> > "Tom Moreau" wrote:
>> >
>> > > You can change the logical file names, using ALTER DATABASE, without
>> > > taking
>> > > the DB's offline.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
>> > > Hi,
>> > >
>> > > I have 3 databases running on Production server. All the databases have
>> > > names like AA, BB and CC with their Logical Data Files and Log files
>> > > named
>> > > as
>> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
>> > > to
>> > > rename it as CC_Data and CC_Log to make it consistent. Would this
>> > > renaming
>> > > affect anything on the server?
>> > >
>> > >
>> >
>> >
>>|||Thank you.
"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I click
OK to try and save the 2nd database to the host/instance. I don't see how to
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face :)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||I normally drop the db, then copy it to the new location - data in the
data directory, logs to their directory. Rename the files I copied my
test.mdf and test.ldf to test2.mdf and test2.ldf
Then in SMS do a attach database and browse to the mdf file. Select the
new one. If necessary do the same for the log (you'll have to do this
if you moved it).
* In the top window check the "Attach AS" and change it to whatever you
want - for me test2
* In the bottom window under "Current File Path" browse to the new file
and select the one you renamed.- do for mdf and ldf files. For mdf I
selected test2.mdf and for ldf test2.ldf
* In top window change owner if necessary.
* Click OK
Now go to the database in object explorer and right click->properties.
Check the files window. You'll see the new files selected and if you
want you can change the logical names.
Yabe wrote:
> "Tibor Karaszi" wrote:
>> Does changing the logical name also change the physical (file) name?
>> No.
>>
>> If not,
>> is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>|||Probably just the SSMS GUI that is messing with you. I suggest you read about sp_attach_db in Books
Online. Since the mdf has the path and name of the original ldf (and other db files), you need to
specify all file names, like:
EXEC sp_attach_db 'NewDbName', 'C:\NewPath\NewFileName.mdf', 'C:\NewPath\NewFileName.ldf'
Above from memory, so please check Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:23B5D429-5E6A-4209-BCC2-CB9C20C0C94B@.microsoft.com...
>
> "Tibor Karaszi" wrote:
>> > Does changing the logical name also change the physical (file) name?
>> No.
>>
>> > If not,
>> > is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to
>> change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this
>> very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>

rename logical file

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

Wednesday, March 28, 2012

Renaming a Database Physical and logical file names

Monday, March 26, 2012

rename of logical file name

Friday, March 23, 2012

rename logical name**

rename logical name**

rename logical name**

Rename logical name of a datafile

Rename logical name of a datafile

Rename logical name of a datafile

rename logical file

rename logical file

rename logical file

Removing a server

Blog Archive

About Me