Change the name of the shadow database MDF/LDF file?

jbarron

Sorry if I missed this in the documentation somewhere, but is it possible to change the naming scheme of the MDF & LDF files created in C:\Users\<user>? Currently they take the form <DatabaseName>_<username>_SHADOW.mdf/ldf. I am trying to create several ReadyRoll projects to handle individual schemas in the same database. I think I have all the separation aspects covered but this is killing me on my TFS build process, because the user and the database name are the same across multiple projects.

dnlnln

By default, the script that ReadyRoll generates to create the database (stored within your project as Pre-Deployment\01_Create_Database.sql) is a free-form script that simply contains a single CREATE DATABASE statement without any special clauses or file configurations. Thus the ability to customize the filename is not natively supported by ReadyRoll.

However you can customize the script to try and include the username as part of each of the database's files. For example, the following statement uses dynamic SQL to include the currently logged-in user's details as part of the DB file naming scheme:

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = N'$(DatabaseName)')
BEGIN
	DECLARE @SQL NVARCHAR(MAX)
	SET @SQL = 'CREATE DATABASE [$(DatabaseName)]
	ON PRIMARY
	(
		NAME = DB_Primary,
		FILENAME = ''$(DefaultDataPath)\$(DefaultFilePrefix)_' + SYSTEM_USER + '.mdf''
	),
	FILEGROUP USERDATA DEFAULT
	(
		NAME = DB_UserData,
		FILENAME = ''$(DefaultDataPath)\$(DefaultFilePrefix)_' + SYSTEM_USER + '_UserData.ndf''
	)
	LOG ON
	(
		NAME = DB_Log,
		FILENAME = ''$(DefaultLogPath)\$(DefaultFilePrefix)_' + SYSTEM_USER + '.ldf''
	)';
	EXEC(@SQL);
END
GO

After making changes to the script, you may need to manually drop any existing shadow databases from the instance that your TFS build configuration is connected to. I hope this helps!