Renaming databases and database files in SQL Server
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.
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:
EXEC sp_helpdb 'BankDB'
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.
RESTORE FILELISTONLY FROM DISK = 'C:BankDB_FilesFull_BackupBankDBbackup.bak' GO
FROM DISK = 'C:BankDB_FilesFull_BackupBankDBbackup.bak'
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
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
Now our restored database looks like this: