STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Renaming databases and database files in SQL Server

Overview

Sometimes there is a need to rename a database.  There may be variable causes:

  1. Restoring/moving the database from development/test to the production environment.

  2. The database name doesn’t describe the data in it correctly.

  3. The database was restored with a temporary name and now the time to change it has come.

  4. 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

  1. The database being renamed must be in “SINGLE _USER” mode.

  2. To rename a database you must be a member of the sysadmin or dbcreator fixed server roles.

  3. Database filegroup names will not be changed as a result of renaming the database

  4. If you have code references to the old database name, change the code to reference the new database name.

  5. 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:

  1. Right click on databases, choose ‘restore database’.

  2. 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).

  3. Go to Options and change the name and/or location of the physical files (.mdf, .ldf) and click OK.

Change Logical file name:

  1. After restore process is finished, go to your new database in Management Studio.

  2. Right click on the database (BankArchive) to choose properties.

  3. In properties window, select Files page.

  4. 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

#tsql #databaseadministration #databaseadministrationservice

JOIN OUR MAILING LIST

CONTACT US

4 Itzhak Ben Zvi, Hod-Ha'sharon,

Israel 4537302

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle