Overview
Sometimes there is a need to rename a database. There may be variable causes:
Restoring/moving the database from development/test to the production environment.
The database name doesn’t describe the data in it correctly.
The database was restored with a temporary name and now the time to change it has come.
The database file name is different from database name.
In this article, we will describe how you can change the database name, database logical file name and database file name (.mdf, .ldf).
There are a few ways to do this. We will rename the database during the restoring process and rename of existing database using T-sql.
Restrictions
The database being renamed must be in “SINGLE _USER” mode.
To rename a database you must be a member of the sysadmin or dbcreator fixed server roles.
Database filegroup names will not be changed as a result of renaming the database
If you have code references to the old database name, change the code to reference the new database name.
The name of the database must be unique within the instance. To check which databases you have on your instance on, use sp_databases
Database rename during restoring process
Let’ assume that we have a backup of database named BankDB and we want to restore a database named BankArchive from it.
BankDB_Test looks like this:
PgSQL
EXEC sp_helpdb 'BankDB'
1
EXEC sp_helpdb 'BankDB'
BankDB backup is located on: C:BankDB_FilesFull_BackupBankDBbackup.bak Now we will restore the database, and during restoring, we will change file locations and change the database name. RESTORE FILELISTONLY returns a result set to contain a list of the database and log files, contained in the backup set. We will use it to check the logical file names to use in restoring the database process. We can’t rename logical file names in the backup.
PgSQL
RESTORE FILELISTONLY FROM DISK = 'C:BankDB_FilesFull_BackupBankDBbackup.bak' GO
1
2
3
RESTORE FILELISTONLY
FROM DISK = 'C:BankDB_FilesFull_BackupBankDBbackup.bak'
GO
PgSQL
RESTORE DATABASE [BankArchive] FROM DISK = N'C:BankDB_FilesFull_BackupBankDBbackup.bak' WITH FILE = 1, MOVE N'BankDB' TO N'C:BankArchive_FilesBankArchive.mdf', MOVE N'BankDB2' TO N'C:BankArchive_FilesBankArchive2.ndf', MOVE N'BankDB3' TO N'C:BankArchive_FilesBankArchive3.ndf', MOVE N'BankDB_Log' TO N'C:BankArchive_FilesBankArchive_Log.ldf', NOUNLOAD, REPLACE, STATS = 10 GO
1
2
3
4
5
6
7
8
9
RESTORE DATABASE [BankArchive]
FROM DISK = N'C:BankDB_FilesFull_BackupBankDBbackup.bak'
WITH FILE = 1,
MOVE N'BankDB' TO N'C:BankArchive_FilesBankArchive.mdf',
MOVE N'BankDB2' TO N'C:BankArchive_FilesBankArchive2.ndf',
MOVE N'BankDB3' TO N'C:BankArchive_FilesBankArchive3.ndf',
MOVE N'BankDB_Log' TO N'C:BankArchive_FilesBankArchive_Log.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
Now our restored database looks like this:
PgSQL
EXEC sp_helpdb 'BankDB'
1
EXEC sp_helpdb 'BankDB'
We can’t change logical file names during the restoring process, but we can do it afterwards:
PgSQL
ALTER DATABASE BankArchive MODIFY FILE (NAME = BankDB, NEWNAME='BankArchive') GO ALTER DATABASE BankArchive MODIFY FILE (NAME = BankDB2, NEWNAME='BankArchive2') GO ALTER DATABASE BankArchive MODIFY FILE (NAME = BankDB3, NEWNAME='BankArchive3') GO ALTER DATABASE BankArchive MODIFY FILE (NAME = BankDB_Log, NEWNAME='BankArchive_Log') GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ALTER DATABASE BankArchive
MODIFY FILE
(NAME = BankDB, NEWNAME='BankArchive')
GO
ALTER DATABASE BankArchive
MODIFY FILE
(NAME = BankDB2, NEWNAME='BankArchive2')
GO
ALTER DATABASE BankArchive
MODIFY FILE
(NAME = BankDB3, NEWNAME='BankArchive3')
GO
ALTER DATABASE BankArchive
MODIFY FILE
(NAME = BankDB_Log, NEWNAME='BankArchive_Log')
GO
Now let’s check our database:
PgSQL
EXEC sp_helpdb 'BankDB'
1
EXEC sp_helpdb 'BankDB'
You can see that logical file name changed as well.
You can perform this operation with Management Studio as well:
Restore:
Right click on databases, choose ‘restore database’.
In the Restore Database window, write the name of your destination database (BankArcive), and then specify the location of backup sets to restore (database or device).
Go to Options and change the name and/or location of the physical files (.mdf, .ldf) and click OK.
Change Logical file name:
After restore process is finished, go to your new database in Management Studio.
Right click on the database (BankArchive) to choose properties.
In properties window, select Files page.
In Files page you can see all database files. Go to the logical name column, change the name of logical file and click OK.
Rename of existing database
We have a database named BankBD and all the data in it is an archive, so we want to rename the database as BankArchive.
First, we will rename logical files. To do that we’ll check how many files do we have named:
PgSQL
EXEC sp_helpdb 'BankDB' GO
1
2
EXEC sp_helpdb 'BankDB'
GO
We have four logical files to rename. Let’s rename them:
PgSQL
ALTER DATABASE BankDB MODIFY FILE (NAME = BankDB, NEWNAME='BankArchive') GO ALTER DATABASE BankDB MODIFY FILE (NAME = BankDB2, NEWNAME='BankArchive2') GO ALTER DATABASE BankDB MODIFY FILE (NAME = BankDB3, NEWNAME='BankArchive3') GO ALTER DATABASE BankDB MODIFY FILE (NAME = BankDB_Log, NEWNAME='BankArchive_Log') GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ALTER DATABASE BankDB
MODIFY FILE
(NAME = BankDB, NEWNAME='BankArchive')
GO
ALTER DATABASE BankDB
MODIFY FILE
(NAME = BankDB2, NEWNAME='BankArchive2')
GO
ALTER DATABASE BankDB
MODIFY FILE
(NAME = BankDB3, NEWNAME='BankArchive3')
GO
ALTER DATABASE BankDB
MODIFY FILE
(NAME = BankDB_Log, NEWNAME='BankArchive_Log')
GO
Now BankDB database looks like this:
To change a database name we must disconnect other users and prevent them from connecting. We can use one of the following options:
PgSQL
EXEC sp_dboption BankDB, 'SINGLE USER', TRUE GO
1
2
EXEC sp_dboption BankDB, 'SINGLE USER', TRUE
GO
Or this:
PgSQL
ALTER DATABASE BankDB SET SINGLE_USER
1
ALTER DATABASE BankDB SET SINGLE_USER
You must know that if SINGLE_USER is specified and there are other users connected to the database, the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To disconnect all users, we can use the termination option WITH ROLLBACK IMMEDIATE in ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the BankDB database will be immediately disconnected.
PgSQL
ALTER DATABASE BankDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
1
2
3
ALTER DATABASE BankDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
Now we can change the database name:
PgSQL
EXEC sp_renamedb 'BankDB', 'BankArchive' GO
1
2
EXEC sp_renamedb 'BankDB', 'BankArchive'
GO
Or like this:
PgSQL
ALTER DATABASE BankDB MODIFY NAME = BankArchive
1
2
ALTER DATABASE BankDB
MODIFY NAME = BankArchive
Now the database looks like this:
PgSQL
EXEC sp_helpdb 'BankArchive' GO
1
2
EXEC sp_helpdb 'BankArchive'
GO
Now, all we have left is to rename the physical files. To do that we have to detach the database from SQL server, change the file names and attach the files with new names to SQL server.
PgSQL
USE master; GO EXEC sp_detach_db @dbname = 'BankArchive' GO
1
2
3
4
5
USE
master;
GO
EXEC sp_detach_db @dbname = 'BankArchive'
GO
Now we can rename the files. There a few ways to do that:
First way to rename files with xp_cmdshell in Management Studio:
PgSQL
USE master; GO EXEC sp_configure 'show advanced options' , 1 RECONFIGURE GO EXEC sp_configure 'xp_cmdshell' , 1 RECONFIGURE GO EXEC xp_cmdshell 'md C:BankArchive_Files' GO EXEC xp_cmdshell 'ren C:BankDB_FilesBankDB.mdf BankArchive.mdf' GO EXEC xp_cmdshell 'ren C:BankDB_FilesBankDB2.ndf BankArchive2.ndf' GO EXEC xp_cmdshell 'ren C:BankDB_FilesBankDB3.ndf BankArchive3.ndf' GO EXEC xp_cmdshell 'ren C:BankDB_FilesBankDB_Log.ldf BankArchive_Log.ldf' GO EXEC sp_configure 'xp_cmdshell' , 0 RECONFIGURE GO EXEC sp_configure 'show advanced options' , 0 RECONFIGURE GO
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
USE
master;
GO
EXEC sp_configure 'show advanced options' , 1
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell' , 1
RECONFIGURE
GO
EXEC xp_cmdshell
'md C:BankArchive_Files'
GO
EXEC xp_cmdshell
'ren C:BankDB_FilesBankDB.mdf BankArchive.mdf'
GO
EXEC xp_cmdshell
'ren C:BankDB_FilesBankDB2.ndf BankArchive2.ndf'
GO
EXEC xp_cmdshell
'ren C:BankDB_FilesBankDB3.ndf BankArchive3.ndf'
GO
EXEC xp_cmdshell
'ren C:BankDB_FilesBankDB_Log.ldf BankArchive_Log.ldf'
GO
EXEC sp_configure 'xp_cmdshell' , 0
RECONFIGURE
GO
EXEC sp_configure 'show advanced options' , 0
RECONFIGURE
GO
Another way is to use cmd: ren C:BankDB_FilesBankDB.mdf BankArchive.mdf ren C:BankDB_FilesBankDB2.ndf BankArchive2.ndf ren C:BankDB_FilesBankDB3.ndf BankArchive3.ndf ren C:BankDB_FilesBankDB_Log.ldf BankArchive_Log.ldf
Another way is to go to the file and simply rename it with right click rename. We can also move the files to another location if we want to.
After renaming physical files we have to attach database to SQL Server.
PgSQL
EXEC sp_attach_db @dbname = 'BankArchive' , @filename1 = 'C:BankDB_FilesBankArchive.mdf' , @filename2 = 'C:BankDB_FilesBankArchive2.ndf' , @filename3 = 'C:BankDB_FilesBankArchive3.ndf' , @filename4 = 'C:BankDB_FilesBankArchive_Log.ldf' GO
1
2
3
4
5
6
EXEC sp_attach_db @dbname = 'BankArchive'
, @filename1 = 'C:BankDB_FilesBankArchive.mdf'
, @filename2 = 'C:BankDB_FilesBankArchive2.ndf'
, @filename3 = 'C:BankDB_FilesBankArchive3.ndf'
, @filename4 = 'C:BankDB_FilesBankArchive_Log.ldf'
GO
Now our database is completely renamed:
PgSQL
EXEC sp_helpdb 'BankArchive' GO
1
2
EXEC sp_helpdb 'BankArchive'
GO
Don’t forget to set SINGLE_USER option off.
PgSQL
ALTER DATABASE Bankarchive SET MULTI_USER
1
2
ALTER DATABASE Bankarchive
SET MULTI_USER
Or:
PgSQL
EXEC sp_dboption NewDB, 'SINGLE USER', FALSE
1
EXEC sp_dboption NewDB, 'SINGLE USER', FALSE