What are the challenges you face when working across database platforms? Take the survey
Options

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

jbarronjbarron Posts: 2 New member
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.
Tagged:

Comments

  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    edited June 19, 2017 5:36AM
    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!
    Daniel Nolan
    Product Manager
    Redgate Software
Sign In or Register to comment.