Changing the name of a SQL database

问题:

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

回答1:

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

回答2:

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!

回答3:

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

sp_renamedb 'olddatabasename','newdatabasename'

but make sure database is not in use.

回答4:

• 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 can’t use the GUI for the re-attach

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

Refresh your SSMS and you are all done.

References

• Database Detach and Attach (SQL Server)
• Attach a Database

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.