change-sql-server-database-name-with-related-filenames-changing

Change Sql Server Database Name with Related Filenames Changing

2014-12-23

Sometimes we need to change a database name, for example, we have a database, but later we have newer version database based on this database, we want to keep both of versions on same SQL Server machine, and also we want to use keep the database name on newer version. What the one way which we can do is rename the old one.

But if you simply changed the database name in SQL Server Management Studio, we will find the Studio only changed the database name but did not change all related database file names such as logic name, .mdf file name and _log.ldf file name, so then we can not simply restore our newer database version with keeping the existing database name otherwise you will overwrite old database.

The following is the correct steps to rename a SQL Server databaes:

1: Open SQL Server Management Studio, right click old database and rename it, for example, change the S950DB to S950DB_bak;

2: The related file names still not changed now, so using the following script in SQL Server Management Studio:

(you have to change the following file directors using your own, find where the mdf file located on your computer)

USE [S950DB_bak];
ALTER DATABASE S950DB_bak MODIFY FILE (NAME = �S950DB�, FILENAME = �C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\S950DB_bak.mdf�);
ALTER DATABASE S950DB_bak MODIFY FILE (NAME = �S950DB_log�, FILENAME = �C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\S950DB_bak_log.ldf�);
ALTER DATABASE S950DB_bak MODIFY FILE (NAME = S950DB, NEWNAME = S950DB_bak);
ALTER DATABASE S950DB_bak MODIFY FILE (NAME = S950DB_log, NEWNAME = S950DB_bak_log);

3: Still in Studio, right click S950DB_bak, select Tasks > Take Offline;

Now you will see the old database offline status:

4: Go to the folder which your mdf and log.ldf file located, manually rename those two files to new file names:

5: Now go back to SQL Server Management Studio, right click S950DB_bak and bring it back online: Taskes > Bring Online:

Now your SQL Server database completely renamed. You can restore the newer version of database now.