[ACCEPTED]-Changing the name of a SQL database-sql-server-2008
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
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.
You 14 could Detach the database, rename the files 13 and then Attach the database pointing to 12 the renamed files to do so.
You could Backup 11 the database and then restore, changing 10 the file location during the restore process.
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
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.
- 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)
- Rename Physical files: Once the database is detached the physical files are unlocked and you can rename them using Windows Explorer:
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
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!
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
but 1 make sure database is not in use.
- 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.
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