[ACCEPTED]-Changing the name of a SQL database-sql-server-2008

Accepted answer
Score: 10

Taken verbatim from here:

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

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

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

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

  4. using 9 T SQL

    ALTER DATABASE databaseName SET OFFLINE GO

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

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

    ALTER 2 DATABASE databaseName SET ONLINE GO

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

Score: 7

Be careful when you use Rename option from context menu that appear when you right click on the 11 database in Management Studio. This option 10 does not change the database file names. To 9 change logical filenames for DATA and LOG 8 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: enter image description here
  3. Attaching database with New Name: For it use T SQL:

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

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

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

It 1 should works!

Score: 1

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

sp_renamedb 'olddatabasename','newdatabasename'

but 1 make sure database is not in use.

Score: 0
  • You can rename the database with SSMS; rclick database > rename
  • You can change the logical name with SSMS; Database > Properties > Files > edit the logical names
  • Detach database with SSMS; Database > Tasks > Detach…
  • You can change the file names with Windows Explorer (after detach)
  • Once the file names have been changed you 5 can’t use the GUI for the re-attach

    CREATE 4 DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
    (FILENAME 3 = 'C:\MySQLServer\AdventureWorks_Log.ldf')
    FOR 2 ATTACH;

Refresh your SSMS and you are all 1 done.

References

Note: If you prefer to type as little code as possible. Once the database is detached, use the GUI to begin the re-attach process before you change the file names. Use the "Script Action to..." and get the code. After changing the file names with windows explorer, update them in the code in SSMS and run it.

More Related questions