Author |
Topic |
WindChaser
Posting Yak Master
225 Posts |
Posted - 2010-06-14 : 17:02:32
|
Hi folks,This should be easy but I'm hitting a wall. I have a database called MyDB.mdf which should have a log called MyDB.ldf. But for some reason, the client has a log called MyDB_Log.ldf. I just need to change the name of the log back to what it should be. I tried SP_Rename and Alter Database Modify but can't figure the exact syntax I suppose. Any help will be greatly appreciated. Thanks! |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2010-06-14 : 17:11:43
|
Just the file name. When a backup restoration is performed, everything seems to work perfectly so the logical name is fine.Thanks Tara. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2010-06-14 : 17:53:47
|
Tara,So I executed alter database MYDB MODIFY FILE ( NAME = MYDB_Log, FileNAME = 'C:\MyPath\MyDB.ldf' ) and got "The file "MyDB_Log" has been modified in the system catalog. The new path will be used the next time the database is started."But when I want to access the database, I get "TITLE: Microsoft SQL Server Management Studio------------------------------Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)Cannot create file 'C:\ExcelDent\LogisticsSQL_log.LDF' because it already exists. Change the file path or the file name, and retry the operation. (Microsoft SQL Server, Error: 5170)As it turns out, the file name had not been changed in the folder so I manually rename it as proposed in the above error message. When I tried to access it again, I got the following:Cannot show requested dialog.An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)Database 'LogisticsSQL' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)" I'm not sure what's wrong here... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2010-06-14 : 18:08:56
|
Nah, don't worry about the database. I can restore it from the backup in a snap. That's what I've been doing for the last 3 hours trying to figure out how to rename the log properly! But I'm no closer to reaching the goal... frustrating... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Kristen
Test
22859 Posts |
Posted - 2010-06-14 : 18:27:04
|
If you are happy to restore from backup might be easier to assign the new File name and Logical name as part of that process.This may help:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300 |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2010-06-14 : 22:28:37
|
Ah shoot, you're joking! I can't believe it was that simple. And all this time I was mucking around with T-SQL statements. Well, thanks again Tara. You're the best!!! |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2010-06-14 : 22:31:49
|
Just noticed your post Kristen. The Detach-Rename-Attach procedure was sufficient for my trifling requirements. Thanks for your help! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
jasonabarr
Starting Member
1 Post |
Posted - 2010-07-20 : 09:00:53
|
hellototally new to all of this, but I wonder if I need to do the same thing.my database is working at the moment with only a mdf and a _log.ldf in the data folder.however if I want to deattach and reattach using dbattach, it doesn't work, listing 'missing.ldf file' as the main reason.how can i generate a new ldf ie mydatabase.ldf, which will let me use dbattach?any help greatly appreciated.i should add, the only ldf i can find in my backups doesn't want to work using dbattach either, stating it must be corrupt-please try a back...:-(jb |
|
|
|