October 15, 2009 at 7:40 am
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 ?
October 15, 2009 at 7:46 am
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
October 15, 2009 at 7:52 am
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
October 15, 2009 at 7:53 am
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
---------------------------------------------------------------------
October 15, 2009 at 8:00 am
one of these days Gail I will get my reply in just BEFORE you. 🙂
---------------------------------------------------------------------
October 15, 2009 at 8:07 am
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
October 15, 2009 at 8:10 am
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:
---------------------------------------------------------------------
October 15, 2009 at 11:04 pm
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.
October 15, 2009 at 11:16 pm
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...."
October 16, 2009 at 12:50 am
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
October 16, 2009 at 3:39 am
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
Yes.. It is. Thanks Gila.
October 22, 2014 at 4:14 pm
Hi,
I have tried the steps you suggest (and many other options found elsewhere on the web) but have not been able to get any to work. I first followed the guide at the "Moving TempDB - SQL School Video" on this site and then tried your steps.
I either end up with the db in "Recovery Pending" state, or the "Bring Online" fails saying that the "Set Offline failed" even it did not say so when I did it! The mdf/ldf is left unchanged and the only way I have found to recover the situation is to delete the db from within SSMS and the windows folder then copy my backup mdf back in, reattach and then I am back where I started with the un-renamed mdf...
I am a newbie with SQL and am struggling to understand why such a simple sounding task should be so hard (notwithstanding it may be nothing to do with SSMS and I just don't know why)... I have also looked at the file permissions before during after each step and can't see anything changing there (always stay at full permission for all
October 23, 2014 at 3:35 am
I suggest that you post on a new thread.
People hardly monitor old threads like this one.
-- Gianluca Sartori
October 23, 2014 at 7:25 am
Hey,
Thanks for the tip and for noticing a post that no one is likely to monitor. FYI and for anyone else who has a similar problem I managed to get it working by following the suggestion about backing up the db and then restoring it - you get the option to rename the mdf/ldf during the restore and can also rename the logical names after the restore via db properties... 🙂
October 23, 2014 at 9:45 am
another newbie (10/23/2014)
I managed to get it working by following the suggestion about backing up the db and then restoring it - you get the option to rename the mdf/ldf during the restore and can also rename the logical names after the restore via db properties... 🙂
Not the best way to do it, totally unnecessary use of resources.
To rename the files use ALTER DATABASE ... MODIFY FILE ...
Once you have sent the alter commands to the database take it offline, again using ALTER DATABASE ... SET OFFLINE
Rename the OS files to match the command you sent to the database, then bring the database online using ALTER DATABASE ... SET ONLINE
Works flawlessly 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply