SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rename mdf and ldf files ?


Rename mdf and ldf files ?

Author
Message
Tara-1044200
Tara-1044200
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1459 Visits: 3074
1. how do i rename mdf and ldf file names of a database without restoring or creating new database and move the data ?

2. just as we query "
select name from master.dbo.sysdatabases
" for db names is there a way we can list out mdf or ldf file names ?
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3178 Visits: 3200
Tara-1044200 (10/15/2009)
1. how do i rename mdf and ldf file names of a database without restoring or creating new database and move the data ?


Take the database OFFLINE. Go and change the MDF & LDF files name in it's locations and the take the DB online.


for db names is there a way we can list out mdf or ldf file names ?


Try
select name, filename from master.dbo.sysdatabases
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86681 Visits: 45254
San-847017 (10/15/2009)
Take the database OFFLINE. Go and change the MDF & LDF files name in it's locations and the take the DB online.

Do just that and the DB will be suspect when brought back online.

Step 1: Run ALTER DATABASE and use the MOVE option to change the names of the mdf and ldf that is stored in SQL's data catalog
Step 2: Take the DB offline
Step 3: Rename the files themselves
Step 4: Bring the database online

If you leave out either step 1 or step 3, the DB will be suspect when it's brought online.

2. just as we query "
select name from master.dbo.sysdatabases


" for db names is there a way we can list out mdf or ldf file names ?


SELECT * FROM sys.master_files



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10276 Visits: 13687
San-847017 (10/15/2009)
Tara-1044200 (10/15/2009)
1. how do i rename mdf and ldf file names of a database without restoring or creating new database and move the data ?


Take the database OFFLINE. Go and change the MDF & LDF files name in it's locations and the take the DB online.


for db names is there a way we can list out mdf or ldf file names ? [/quote]

make sure you use alter database modify file command between offline and online so SQL knows the new name of the file. Otherwise database won't come back online.

no 2,
select d.name,f.name as logical_name,f.physical_name from sys.master_files f join sys.databases d on f.database_id = d.database_id

---------------------------------------------------------------------
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10276 Visits: 13687
one of these days Gail I will get my reply in just BEFORE you. :-)

---------------------------------------------------------------------
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86681 Visits: 45254
george sibbald-364359 (10/15/2009)
one of these days Gail I will get my reply in just BEFORE you. :-)

Type faster ;-)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10276 Visits: 13687
GilaMonster (10/15/2009)
george sibbald-364359 (10/15/2009)
one of these days Gail I will get my reply in just BEFORE you. :-)

Type faster ;-)


thats what you said last time. Obviously I have failed to improve. Crying

---------------------------------------------------------------------
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3178 Visits: 3200

make sure you use alter database modify file command between offline and online so SQL knows the new name of the file. Otherwise database won't come back online.


Sorry !...Missed this step.
Thanks for correcting.
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3178 Visits: 3200

If you leave out either step 1 or step 3, the DB will be suspect when it's brought online.


Gila, Infact it's not coming online at all. Getting the error saying "Unable to open physical file...."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86681 Visits: 45254
San-847017 (10/15/2009)

If you leave out either step 1 or step 3, the DB will be suspect when it's brought online.


Gila, Infact it's not coming online at all. Getting the error saying "Unable to open physical file...."


Correct, it cannot come online.

It doesn't leave the DB in the offline state, doesn't put it online. In fact (if you check the sys.databases view) the state of a DB after doing this is RECOVERY_PENDING. Not actually the SUSPECT state, but it means almost the same thing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search