SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Rename Log file only
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WindChaser
Posting Yak Master

216 Posts

Posted - 06/14/2010 :  17:02:32  Show Profile  Reply with Quote
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

USA
36685 Posts

Posted - 06/14/2010 :  17:08:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do you only care about the file name or do you care about the logical name too?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 06/14/2010 17:09:06
Go to Top of Page

WindChaser
Posting Yak Master

216 Posts

Posted - 06/14/2010 :  17:11:43  Show Profile  Reply with Quote
Just the file name. When a backup restoration is performed, everything seems to work perfectly so the logical name is fine.

Thanks Tara.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 06/14/2010 :  17:19:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
ALTER DATABASE databaseNameGoesHere
MODIFY FILE (NAME = 'LogicalNameGoesHere', FILENAME = 'PathAndFileNameGoesHere')

Such as:
ALTER DATABASE db1
MODIFY FILE (NAME = 'db1_Log', FILENAME = 'F:\Data\db1.ldf')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

WindChaser
Posting Yak Master

216 Posts

Posted - 06/14/2010 :  17:53:47  Show Profile  Reply with Quote
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...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 06/14/2010 :  17:57:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yikes! You should not have manually changed the filename. This is very important:

quote:

The new path will be used the next time the database is started.



I'm not sure what state your database is in now. You could try changing the filename back, restart the service, and then verify the filename and database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

WindChaser
Posting Yak Master

216 Posts

Posted - 06/14/2010 :  18:08:56  Show Profile  Reply with Quote
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...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 06/14/2010 :  18:24:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can easily do it with BACKUP/RESTORE or detach/attach.

Detach database
Rename file
Attach database

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/14/2010 :  18:27:04  Show Profile  Reply with Quote
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
Go to Top of Page

WindChaser
Posting Yak Master

216 Posts

Posted - 06/14/2010 :  22:28:37  Show Profile  Reply with Quote
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!!!
Go to Top of Page

WindChaser
Posting Yak Master

216 Posts

Posted - 06/14/2010 :  22:31:49  Show Profile  Reply with Quote
Just noticed your post Kristen. The Detach-Rename-Attach procedure was sufficient for my trifling requirements. Thanks for your help!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 06/15/2010 :  00:35:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jasonabarr
Starting Member

1 Posts

Posted - 07/20/2010 :  09:00:53  Show Profile  Reply with Quote
hello
totally 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


Edited by - jasonabarr on 07/20/2010 09:07:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000