Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2011-02-03 : 12:02:58
|
| Hi,I noticed that one of sql server 2005 database has mdf and ndf files (no ldf)When restored the database someone made mistake, instead of typing .ldf, .ndf is added in log file.Now, how to replace the .ndf file with .ldfPlease advice. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-03 : 15:34:20
|
| ALTER DATABASE {database} MODIFY FILE (...);GOALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE; --take database offline for renameGO-- rename the file to the same name you used in the MODIFY FILE commandALTER DATABASE {database} SET ONLINE;GOThe advantage to this process is that you don't have to worry about resetting the owner after the attach.Jeff |
 |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-03-08 : 10:31:18
|
| <-- rename the file to the same name you used in the MODIFY FILE command>you mean , rename file in disk path (eg:C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Test.mdf) |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-03-08 : 15:48:18
|
| Correct - if your file is currently C:\SQL\MyDatabase.ndf, and you use MODIFY FILE to change it to C:\SQL\MyDatabase.ldf - then, after taking database offline you rename the file to C:\SQL\MyDatabase.ldf and bring the database back online.Jeff |
|
|
|
|
|
|
remove ndf file