SQL Script to Change Compatibility of All Databases

Microsoft Dynamics GPWhen moving databases between servers for an upgrade of Microsoft Dynamics GP, you will, when also upgrading SQL Server, need to change the compatibility level of the databases. Rather than dong this individually, the below script can be run against the system database to generate a script you can then run to update the compatibility level of all databases.

The highlighted variable should be changed to the relevant version.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
DECLARE @CompatibilityLevel VARCHAR(140) = 140


SELECT 'USE master
	GO
	ALTER DATABASE [' + DB_NAME() + '] SET COMPATIBILITY_LEVEL = ' + @CompatibilityLevel + '
	GO'
UNION ALL
	SELECT '
		ALTER DATABASE [' + ['Company Master'].INTERID + '] SET COMPATIBILITY_LEVEL = ' + @CompatibilityLevel + '
		GO'
	FROM
		SY01500 AS ['Company Master']
GO

This script doesn’t get the logical file name, but I’ve hooked it into the series I did on Logical File Name SQL Scripts as it will often be needed at the same time.

Click to show/hide the Logical File Name SQL Scripts Series Index

SQL Script to Change Database Owner of All Databases

Microsoft Dynamics GPWhen moving databases between servers, you need to make sure the databases are set to have an owner of DYNSA. Rather than dong this individually, the below script can be run against the system database to generate a script you can then run to update the db owner.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
SELECT 'USE [' + DB_NAME() + ']
	GO
	sp_changedbowner ''DYNSA''
	GO'
UNION
	SELECT 'USE [' + ['Company Master'].INTERID + ']
		GO
		sp_changedbowner ''DYNSA''
		GO'
	FROM
		SY01500 AS ['Company Master']
GO

This script doesn’t get the logical file name, but I’ve hooked it into the series I did on Logical File Name SQL Scripts as it will often be needed at the same time.

Click to show/hide the Logical File Name SQL Scripts Series Index

Logical File Name SQL Scripts: SQL Script to Create Restore Scripts for all Dynamics Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical file name will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script creates restore scripts for all Dynamics GP databases and will use the correct logical filename whether it matches the . The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change the highlighted section:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
USE DYNAMICS
GO

DECLARE @BackupLocation VARCHAR(300) = 'I:\Live Upgrade\2016R2\'
DECLARE @DataLocation VARCHAR(300) = 'S:\Data\'
DECLARE @LogLocation VARCHAR(300) = 'L:\Logs\'
DECLARE @DateParameter VARCHAR(10) = FORMAT(GETDATE(), 'yyyy-MM-dd')
DECLARE @SQLStatement NVARCHAR(MAX)
SELECT @SQLStatement =	'
	USE master
	GO
	RESTORE DATABASE ' + DB_NAME() + ' FROM  
	DISK = N''' + @BackupLocation + DB_NAME() + '_' + @DateParameter + '.bak'' WITH  FILE = 1,  
	MOVE N''' + ['databases-data'].name + ''' TO N''' + @DataLocation + RIGHT(['databases-data'].physical_name, CHARINDEX('\', REVERSE(['databases-data'].physical_name)) - 1) + ''',  
	MOVE N''' + ['databases-log'].name + ''' TO N''' + @LogLocation + RIGHT(['databases-log'].physical_name, CHARINDEX('\', REVERSE(['databases-log'].physical_name)) - 1) + ''',  NOUNLOAD,  STATS = 5
	GO
	'
FROM
	sys.master_files AS ['databases-data']
INNER JOIN
	sys.master_files AS ['databases-log']
		ON
			DB_NAME(['databases-log'].database_id) = DB_NAME(['databases-data'].database_id)
WHERE
	DB_NAME(['databases-data'].database_id) = DB_NAME() and ['databases-data'].name like '%.mdf'

SELECT @SQLStatement = @SQLStatement + 
	'RESTORE DATABASE ' + RTRIM(['Company Master'].INTERID) + ' FROM  
	DISK = N''' + @BackupLocation + RTRIM(['Company Master'].INTERID) + '_' + @DateParameter + '.bak'' WITH  FILE = 1,  
	MOVE N''' + ['databases-data'].name + ''' TO N''' + @DataLocation + RIGHT(['databases-data'].physical_name, CHARINDEX('\', REVERSE(['databases-data'].physical_name)) - 1) + ''',  
	MOVE N''' + ['databases-log'].name + ''' TO N''' + @LogLocation + RIGHT(['databases-log'].physical_name, CHARINDEX('\', REVERSE(['databases-log'].physical_name)) - 1) + ''',  NOUNLOAD,  STATS = 5
	GO
	'
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files AS ['databases-data']
		ON
			DB_NAME(['databases-data'].database_id) = ['Company Master'].INTERID AND ['databases-data'].name LIKE '%mdf'
INNER JOIN
	sys.master_files AS ['databases-log']
		ON
			DB_NAME(['databases-log'].database_id) = ['Company Master'].INTERID AND ['databases-log'].name LIKE '%ldf'
WHERE
	['databases-data'].name NOT LIKE '%<TEST>'
AND
	['Company Master'].CMPANYID > 0
ORDER BY
	['Company Master'].INTERID
	,['databases-data'].name

SELECT @SQLStatement

Click to show/hide the Logical File Name SQL Scripts Series Index

Logical File Name SQL Scripts: SQL Script to Create Backup Scripts for all Dynamics Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical file name will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script creates backup scripts for all Dynamics GP databases. The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change the highlighted section:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
USE DYNAMICS
GO

DECLARE @BackupLocation VARCHAR(300) = 'I:\Live Upgrade\2016R2\'
DECLARE @DateParameter VARCHAR(10) = FORMAT(GETDATE(), 'yyyy-MM-dd')
DECLARE @SQLStatement NVARCHAR(MAX) =	'
	USE master
	GO
	BACKUP DATABASE ' + DB_NAME() + ' TO
	DISK = N''' + @BackupLocation + DB_NAME() + '_' + @DateParameter + '.bak'' WITH NOFORMAT, INIT,    
	NAME = N''' + DB_NAME() + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
	GO
	'

SELECT @SQLStatement = @SQLStatement +
	'BACKUP DATABASE ' + RTRIM(['Company Master'].INTERID) + ' TO
	DISK = N''' + @BackupLocation + RTRIM(['Company Master'].INTERID) + '_' + @DateParameter + '.bak'' WITH NOFORMAT, INIT,    
	NAME = N''' + RTRIM(['Company Master'].INTERID) + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
	GO
	'
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files ['databases-data']
		ON
			DB_NAME(['databases-data'].database_id) = ['Company Master'].INTERID AND ['databases-data'].name LIKE '%mdf'
WHERE
	['databases-data'].name NOT LIKE '%<TEST>'
AND
	['Company Master'].CMPANYID > 0
ORDER BY
	['Company Master'].INTERID
	,['databases-data'].name

SELECT @SQLStatement

Click to show/hide the Logical File Name SQL Scripts Series Index

Logical File Name SQL Scripts: SQL Script to Alter Logical Filenames of all Dynamics GP Company Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical file name will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script creates an alter script for all Dynamics GP company databases’ logical file names which will ensure that all databases have a logical filename which matches the physical name. The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change the highlighted section:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
USE DYNAMICS
GO

SELECT
	CASE WHEN ['databases'].name LIKE '%Dat%' THEN
	'ALTER DATABASE
		' + ['Company Master'].INTERID + '
	MODIFY FILE 
		(
		NAME = [' + RTRIM(['databases'].name) + ']
		,NEWNAME = [GPS' + RTRIM(['Company Master'].INTERID) + 'Dat.mdf]
		)
	 GO'
	ELSE
	'ALTER DATABASE
		' + ['Company Master'].INTERID + '
	MODIFY FILE 
		(
		NAME = [' + RTRIM(['databases'].name) + ']
		,NEWNAME = [GPS' + RTRIM(['Company Master'].INTERID) + 'Log.ldf]
		)
	 GO'
	 END
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files AS ['databases']
		ON
			DB_NAME(['databases'].database_id) = ['Company Master'].INTERID
WHERE
	['Company Master'].CMPANYID > 0
AND
	(RTRIM(['databases'].name) <> 'GPS' + RTRIM(['Company Master'].INTERID) + 'Dat.mdf'
AND
	RTRIM(['databases'].name) <> 'GPS' + RTRIM(['Company Master'].INTERID) + 'Logldf')
ORDER BY
	['Company Master'].INTERID
	,['databases'].name

Logical File Name SQL Scripts: SQL Script to Return Logical and Physical Names for Dynamics GP Company Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical filename will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script gets the logical file names for all Dynamics GP company databases attached to a system database which can be used to update the restore script so you can easily create backup scripts rather than having to go through the Restore Database for each database individually.

The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change the highlighted section:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
USE DYNAMICS
GO

SELECT
	['Company Master'].INTERID AS InterID
	,['Company Master'].CMPNYNAM AS CompanyName
	,['databases'].name AS LogicalFileName
	,['databases'].physical_name AS PhysicalFileName 
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files ['databases']
		ON
			DB_NAME(['databases'].database_id) = ['Company Master'].INTERID
WHERE
	['Company Master'].CMPANYID > 0
ORDER BY
	['Company Master'].INTERID
	,['databases'].name

This gets the logical file names which can be used to update the restore script so you can easily create backup scripts rather than having to go through the Restore Database for each database individually.

Logical File Name SQL Scripts: SQL Script to Return Logical and Physical Names for Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical filename will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
SELECT
	DB_NAME(database_id) AS DatabaseName
	,name AS LogicalFileName
	,physical_name AS PhysicalFileName 
FROM
	sys.master_files
ORDER BY
	DB_NAME(database_id)

This gets the logical file names which can be used to update the restore script so you can easily create backup scripts rather than having to go through the Restore Database for each database individually.

Click to show/hide the Logical File Name SQL Scripts Series Index

Logical File Name SQL Scripts: Series Index

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical filename will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

I started off with a simple script and then developed it through to generating the backup and restore scripts directly.

This small series includes the four scripts I created.

Click to show/hide the Logical File Name SQL Scripts Series Index

Error Granting Database Access In SQL Server Management Studio

Microsoft Dynamics GPI’m currently involved with a couple of projects where we have needed to migrate databases from one SQL Server to another and needed to do so more than once.

Prior to the first copy, we used a script to load the users into SQL Server and then restored the databases and had no problems.

Later on, after changing some of the permissions to databases we had need to remigrate some of them over. However, we found that users could not access some databases and when we tried to add them we got the following error;

Msg 15023, Level 16, State 1, Line 1
User, group, or role 'pidev' already exists in the current database.

It was looking very like we would need to go through each database and remove access and then regrant it all; a labourious process. A quick Google found me a page from Julian Kuiters which introduced me to the ALTER USER command. The following code will run against the selected database and tie together the SQL Server user with the user in the database without any other work;

USE [GPNL]
GO

ALTER USER pidev WITH LOGIN = pidev
GO

The first highlighted element needs to be changed to the database you want to update the user on and then the following two entries are the usernames which need to be linked (these should be set to the same value).

Update: if your usernames contain a space (e.g. I Grieve) then wrap them in square brackets: e.g.