Sometimes you need to rename a SQL Server database. Beside the database name itself, this name is usually a part of logical database file names, physical file names, maybe file groups.
To rename the database name you need:
- Bring database offline;
- Change paths to database files in system catalog;
- Rename physical file names using Windows Explorer;
- Bring database online;
- Change database name;
- Change logical file names (optionally).
Here is the script to do this. Be careful to put the correct logical database file names.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
-- 0. Remember logical file names and paths USE Old_database; GO SELECT file_id, name AS [Logical name], physical_name AS [File path] FROM sys.database_files; GO -- 1. Bring database offline USE [master]; GO ALTER DATABASE Old_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE Old_database SET OFFLINE; GO -- 2. Change paths to database files in system catalog ALTER DATABASE Old_database MODIFY FILE ( -- logical file name NAME = 'Old_database', -- new path FILENAME = 'C:\DATA\New_database.mdf' ); GO ALTER DATABASE Old_database MODIFY FILE ( -- logical file name NAME = 'Old_database_log', -- new path FILENAME = 'C:\DATA\New_database_log.ldf' ); GO -- 3. Rename physical file names using Windows Explorer PRINT 'RENAME FILES IN FOLDER MANUALLY!!!' GO -- 4. Bring database online ALTER DATABASE Old_database SET ONLINE; GO ALTER DATABASE Old_database SET MULTI_USER; GO -- 5. Change database name ALTER DATABASE Old_database MODIFY NAME = New_database; GO -- 6. Change logical file names ALTER DATABASE New_database MODIFY FILE ( -- old logical file name NAME = 'Old_database', -- new NEWNAME = 'New_database' ); GO ALTER DATABASE New_database MODIFY FILE ( -- old logical file name NAME = 'Old_database_log', -- new NEWNAME = 'New_database_log' ); GO |