Search This Blog

Rename LDF and MDF files after renaming a database


posted on Thursday, August 9, 2012

When you rename a database in SQL Server, the corresponding .mdf and .ldf will not be automatically renamed. This could be a problem if you create a new database (by restoring it) with the original name since the .mdf and .ldf files will seem to already exist.

This is an easy one but I hadn't done it before and I had to Google it to figure it out, so it might be useful for you. And oh yeah, don't rename the files in Windows Explorer!

Here's how to do it correctly:

As long as the files are in used, you won't be able to rename them. So you will have to detach the database for a minute.

1. Detach the database.
USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks2012';
GO
2. Rename the files.
3. Attach the database again, referring to the correct files.
USE master;
GO
CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
    FOR ATTACH;
GO

Source:
http://msdn.microsoft.com/en-us/library/ms187858.aspx and
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/8a696659-02ed-4d9f-af69-aef92eb94995

Could be useful, right?


No comments:

Post a Comment