Ask your technical questions on forums or here :
ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK SPEAKING MVC ASP.NET JQUERY VIDEOS EBOOK ARCHIVE

10 Mar 2016

Rename database data files and logical files in SQL Server

When I rename database using SQL Server Management Studio I always change its data files (mdf, ldf) name as well as logical file name of the resource. And this is very common need for SQL Server guy.

Here's the script which you can use to do this easily.

Rename Files

-- Note: must backup your database

ALTER DATABASE databaseName SET OFFLINE
GO

-- databaseName => this is instance name of the database i.e. regular database name which you can see in database list, you may have changed this and would like to change file names as well
-- logicalDatabaseName => to see it right click on database | properties | select files | and you will see logical database name for mdf and ldf files

ALTER DATABASE databaseName MODIFY FILE (NAME = logicalDatabaseName, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\new_database_name.mdf')
GO

ALTER DATABASE  databaseName MODIFY FILE (NAME = logicalDatabaseName_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\new_database_name_log.ldf')
GO

ALTER DATABASE databaseName SET ONLINE
GO

-- if you face issue like 'Recovery Pending' just restore database using the backup and then follow same steps from the beginning, i noticed this appears in first go but after restore this will work


Rename Logical Files

At the end you should right click on database | properties | files | and you will see logical database name change this as well.

Now refresh your server explorer to see the changes.

Hope this helps.

No comments:

Post a Comment