Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rename mdf and ldf files ? Expand / Collapse
Author
Message
Posted Thursday, October 15, 2009 7:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 9, 2014 7:44 AM
Points: 988, Visits: 2,945
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 ?
Post #803441
Posted Thursday, October 15, 2009 7:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:08 AM
Points: 1,500, Visits: 2,523
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
Post #803446
Posted Thursday, October 15, 2009 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #803451
Posted Thursday, October 15, 2009 7:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,975, Visits: 12,878
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


---------------------------------------------------------------------

Post #803452
Posted Thursday, October 15, 2009 8:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,975, Visits: 12,878
one of these days Gail I will get my reply in just BEFORE you.

---------------------------------------------------------------------

Post #803461
Posted Thursday, October 15, 2009 8:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #803468
Posted Thursday, October 15, 2009 8:10 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,975, Visits: 12,878
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.


---------------------------------------------------------------------

Post #803470
Posted Thursday, October 15, 2009 11:04 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:08 AM
Points: 1,500, Visits: 2,523

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.


Post #803946
Posted Thursday, October 15, 2009 11:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:08 AM
Points: 1,500, Visits: 2,523

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...."
Post #803949
Posted Friday, October 16, 2009 12:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #803972
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse