Renaming a logfile - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

Renaming a logfile

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-27 : 09:31:25

Hi all,

I've got a logfile with the extension .LMDF, not .ldf.

Is this the correct sequence of steps to correct?

1) Suspend log shipping (this is a secondary)

2) Run this code:
USE master
GO
ALTER DATABASE MyDB
MODIFY FILE (NAME = MyCurrentLogsLogicalName, FILENAME = 'E:SQLData\templog.ldf')
GO

3) Take DB offline;

4) Rename MyDB.lmdf file in Explorer file to MyDB.ldf

5) Put DB back online;

6) Resume log shipping.

Btw, this is SS2K

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 10:06:26

I would do it via detach, rename, attach
or
backup, drop, restore

Whatever you do make sure you take a backup first.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-07-27 : 11:55:13

Hello JaybeeSQL

In log shipping secondary, database either in restoring state or stand by state.
Both case, you could not ALTER DATABASE MODIFY FILE.
Take DB Offline first, then ALTER DATABASE MODIFY FILE.
Just be aware users are using the database. So add command to set single user mode to make sure you can take DB Offline without error.

Here are my steps.

<suspend log shipping SQL Agent Job>

USE MASTER
GO

ALTER DATABASE TestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE TestDatabase
SET OFFLINE;

ALTER DATABASE TestDatabase
MODIFY FILE (NAME='TestDatabase_log', FILENAME='D:\Temp\TestDatabase_log_new.ldf');
GO

<rename physical file>

ALTER DATABASE TestDatabase
SET ONLINE;
GO

ALTER DATABASE TestDatabase SET multi_user
GO

<resume log shipping SQL Agent Job>

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources