SQL server

Renaming a database in SQL server.

Renaming a database can be done using the sp_renamedb command.  sp_renamedb ‘old_name’, ‘new_name’. This will only change the DB name.

Then there is the logical file names and the physical file names. Run sp_helpdb ‘new_name’ to see these values.  To rename the logical name use:

alter database new_name modify file(name=old_name_data,newname=new_name_data)

The old_name_data is the logical name for the data file for the old_name DB. The same will have to be done for the log file logical name and any other data files there are.

To rename the physical file name you can either detach the DB: sp_detachdb ‘new_name’, I usually run a sp_helpdb first so as to identify the location of the data and log files. Then go to the location of the physical files and rename them to the desired names. Then you can re attach the DB using SSMS or TSQL and edit the location of the files to the renamed physical files.

Or, you can take the DB offline: alter database new_name set offline. Then go to the location of the physical files and rename them. Now modify the system catalog to update the new path using TSQL:

alter database new_name modify file(name=new_name_Log,filename=’L:\logs\new_name_1.ldf’). This has to be done for all the data files and log files.

Next bring the DB back online: alter database new_name set online.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s