Category: SQL Server

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

Ian Grieve4 minute read3 Comments

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 (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).
*/
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
PrintFriendlyLinkedin

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

Ian Grieve4 minute read2 Comments

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 (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).
*/
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
PrintFriendlyLinkedin

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

Ian Grieve4 minute read0 comment

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 (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).
*/
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.

PrintFriendlyLinkedin

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

Ian Grieve3 minute read0 comment

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 (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).
*/
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.

PrintFriendlyLinkedin

Logical File Name SQL Scripts: Series Index

Ian Grieve2 minute read2 Comments

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.

PrintFriendlyLinkedin

Automate SQL Server Express Backups

Ian Grieve4 minute read1 Comment

Microsoft SQL ServerWe have a couple of small clients who are hosting Microsoft Dynamics GP on SQL Server Express; this is a supported platform, but does not include SQL Server Agent. This means that backups cannot be automated in SQL Server as it is the Agent which provides this functionality.

However, there are still ways in which backups can be automated; one way is to create a DOS batch file and execute it using Windows Scheduler.

The below is a SQL statement which can be executed to generate the DOS batch file commands to backup all of the databases for a Microsoft Dynamics GP implementation.

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @SQL NVARCHAR(MAX) = 'echo off
cls
echo -- BACKUP DATABASE --
set SERVERNAME=2018SQL1\GP
set DATESTAMP=%DATE:~-4%-%DATE:~3,2%-%DATE:~0,2%
set SYSTEMDATABASENAME=DYNAMICS
set BACKUPFILENAME=C:\SQLServer\Backup\%SYSTEMDATABASENAME%_%DATESTAMP%.bak
echo.
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%SYSTEMDATABASENAME%] TO DISK = N''%BACKUPFILENAME%'' WITH NOFORMAT, INIT, NAME = N''%SYSTEMDATABASENAME%-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo.'

SELECT @SQL = @SQL + STUFF((
					SELECT 
						'
set DATABASENAME=' + RTRIM(INTERID) + '
set BACKUPFILENAME=C:\SQLServer\Backup\%DATABASENAME%_%DATESTAMP%.bak
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N''%BACKUPFILENAME%'' WITH NOFORMAT, INIT, NAME = N''%DATABASENAME%-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo.'
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @SQL

If you are using a named system database, the two highlighted sections need to be amended.

To run the script, set the output to file and then copy and paste the resulting script into a file with the .bat extension; this batch file can then be scheduled to run automatically using Windows Scheduler.

PrintFriendlyLinkedin

Problems Logging into Microsoft Dynamics GP as sa After Password Change

Ian Grieve3 minute read2 Comments

Microsoft Dynamics GPI’ve been working with a client recently on an upgrade to Microsoft Dynamics GP 2018 and all has gone well overall. However, they had a security project running at the same time, where they were changing the passwords for a number of accounts including the SQL Srver sa account.

Unfortunately, the password length they were setting was 20 characters,. They supplied the new password to me, but I found I couldn’t log in. When I then tried to copy and paste the password into the field, nothing happened (which is what clued me into the issue).

The cause of this login issue, is that the Password field on the Users Master (SY01400) table has a maximum Keyable Length of 15 characters, and this restriction is enforced on all user ids entered on the login window.

So for me to complete the final steps of the upgrade, the password for the sa account was changed to a 15 character one; after the upgrade was complete, the password was again changed to a longer one and then the account disabled.

Best practice is to use the sa account to administer only SQL Server and to use either a named user account in Dynamics GP or to use the DYNSA account. I am encouraging all clients to ensure this is the case.

There is a wide range of ways you can better secure your system. One way of working through these is to engage with your partner and another is to purchase the Microsoft Dynamics GP Security and Audit Field Manual: Dynamics GP 2016 book by MVP Mark Polino and Andy Snook. This book is for the 2016 version of Dynamics GP, but still fully applies to Microsoft Dynamics GP 2018 (as well as earlier versions).

PrintFriendlyLinkedin

How to Install Microsoft SQL Server 2017: SSRS 2017 Not Supported on a Domain Controller

Ian Grieve3 minute read1 Comment

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

I often create two types of demo or test environment; one will have multiple virtual machiens createdin order to replicate a typical live environment, and the second has only a single virtual machine.

While prepping a test environment of the latter type in readiness for the launch of Microsoft Dynamics GP 2018 RTM, I encountered a problem with SSRS:

Microsoft SQL Server 2017 Reporting Services installation issue

Microsoft SQL Server 2017 Reporting Services
Setup blocked
The following issues blocked Setup
Installing Reporting Services on a domain controller is not supported

This is a major departure from SQL Server 2016 and previous, all of which allowed SSRS to be installed on a Domain Controller; for most systems this will be absolutely fine, but I’ll no longer be able to use single virtual machine demo or test environments.

PrintFriendlyLinkedin

How to Install Microsoft SQL Server 2017: Configure SQL Server Reporting Services

Ian Grieve2 minute read0 comment

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

In the last post I covered the installation of SQL Server Reporting Services (SSRS); in this post I am going to cover the basic configuration of SSRS.

To complete the configuration, launch the Report Server Configuration Manager; ensure the Server Name is set to the correct name and click the Connect button:

The Report Server Configuration Connection

Continue reading “How to Install Microsoft SQL Server 2017: Configure SQL Server Reporting Services”

PrintFriendlyLinkedin

How to Install Microsoft SQL Server 2017: Install SQL Server Reporting Services

Ian Grieve2 minute read0 comment

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

Microsoft Dynamics GP ships with some Reporting Services Reports by default, so this componenbt of SQL Server also needs to be installed. As with SSMS, SQL Server Reporting Services (SSRS) is not installed by the main installer, but, again like SSMS, it is available via a download accessible tyhrough the setup utility.

To install SSRS, laucnh the main setup utility of SQL Server, click Installation and then select Install SQL Server Reporting Services:

SQL Server Installation Center - Installation

Continue reading “How to Install Microsoft SQL Server 2017: Install SQL Server Reporting Services”

PrintFriendlyLinkedin