Changing the name of a SQL database

Question

What is the correct procedure to rename a database?

Example: I have a database that I can access with SQL Server Management Studio and has a name like "MyDatabase". Phisical files of this database are

• MyDatabase.mdf
• MyDatabase_log.ldf

How can I rename these logical and physical names to "MyNewDatabase"?

thanks for helping

Answer 1

Be careful when you use Rename option from context menu that appear when you right click on the database in Management Studio. This option does not change the database file names. To change logical filenames for DATA and LOG files you can also use a Management Studio interface but unfortunately sometimes it does not work.

Let’s do it properly... it should works always.

1. Detach database: Using Management Studio, right-click on database > go to “Tasks” > “Detach”, click OK to detach a database (note: that the DB can not be used to detach it)
2. Rename Physical files: Once the database is detached the physical files are unlocked and you can rename them using Windows Explorer:

3. Attaching database with New Name: For it use T SQL:

   USE [master] CREATE DATABASE [SqlAndMe] ON ( FILENAME = N’C:\…\NewName.mdf’), ( FILENAME = N’C:\…\NewName_log.LDF’) FOR ATTACH

4. Rename Logical file names: Execute this T SQL:

   USE [NewName] ALTER DATABASE [NewName] MODIFY FILE (NAME=N’OldName’, NEWNAME=N’NewName’) ALTER DATABASE [NewName] MODIFY FILE (NAME=N’OldName_log’, NEWNAME=N’NewName_log’) SELECT name, physical_name FROM [NewName].sys.database_files

It should works!

Answer 2

Taken verbatim from here:

There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline

1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

3. You could Backup the database and then restore, changing the file location during the restore process.

4. using T SQL

   ALTER DATABASE databaseName SET OFFLINE GO

   ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf') GO --if changing log file name

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

   ALTER DATABASE databaseName SET ONLINE GO

for more info http://technet.microsoft.com/en-us/library/ms174269.aspx