Changing The Logical File Names Of A SQL Database

When a database is created, it has a logical name assigned to it which will match the physical name. However, when working with Microsoft Dynamics GP, we often create both a live and test database and then populate the settings in the live database and replicate over the top of the test one.

Or on occasion we have created a template database which then gets copied when a new company is created (this method is often used for clients who have a substantial amount of setup in third party modules which the PSTL Company Copy doesn’t cater for.

The problem with both of these is that when a database is restored elsewhere it brings it’s logical name with it; meaning a mismatch between the logical and physical names which causes problems when backing up and restoring databases.

However, all is not lost; it is possible to change the logical name of a database using a simple SQL script. The script, below, has two ALTER DATABASE commands, one for the data file and the other for the log file.

I am changing the logical name from GPST15R2 to GPSP15R2 on both files (see highlighted text):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
ALTER DATABASE
	P15R2
MODIFY FILE 
	(
	NAME = [GPST15R2Dat.mdf]
	,NEWNAME = [GPSP15R2Dat.mdf]
	)
 GO
 ALTER DATABASE
	P15R2
MODIFY FILE 
	(
	NAME = [GPST15R2Log.ldf]
	,NEWNAME = [GPSP15R2Log.ldf]
	)
GO

As always when running a SQL script against a database, make sure you have a good backup and perform a test afterward to make sure there are no problems.