logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
phaag  
#1 Posted : Friday, July 26, 2019 3:18:08 PM(UTC)
phaag

Rank: Administration

Groups: Administrators, Registered
Joined: 1/2/2018(UTC)
Posts: 9
United States

Restoring a copy of a production database to a test database can be a bit tedious, but it is possible to completely script this process via PowerShell. Because it's scriptable, you could even schedule it to run automatically from Windows task scheduler.

Dependencies, Notes and CYA

In order for the script below to work, you will need to have both the GP PowerShell module and SqlPS modules installed. For GP, this installer is one of the additional products from the installation media. For SQL, from PowerShell running as an administrator, you can get the SqlPS module by running:

Install-Module SqlPS

Also, SqlPS is installed with SQL Server by default, so it will already exist on your actual SQL server.

After overwriting the new database with the backup from the source database, it is necessary to correct references in the new database to the old database name. The script necessary to do that is available here (step 6) and should be saved to C:\Scripts\RestoreInterid.sql. Note: if you use Analytical Accounting, you will need to amend this script to also reset the next IDs that live in the system database.

As written, you will also need to be running it on the same server as a GP client install, although it is also possible to run these commands on another computer using PowerShell remoting, as the GP PowerShell documentation itself notes and links to this blog for details.

Bear in mind that there are a number of ISVs (and some included modules, like AA) that need to be initialized in a new company before it is overwritten with a restore from another company, so this is probably easiest to use when overwriting an existing database.

Finally, this script will delete a database, so always make sure you have good backups of any environment you might run it against.

The Script

If you want, you can save the script below to a PowerShell file (extension .ps1) and then right-click it to run everything together. Make sure you replace anything in {} with the actual values.

Code:

#### parameters #####
$serverInstance = "{Sql Server name\instance if applicable}"
$sourceDBName = "{name of database to restore from}"
$fullBackupFileName = "{full path to backup file, like C:\Temp\TWO.bak}"
$newCompanyName = "Test company <test>"
$newDBName = "TEST"
$gpClientDirectory = "C:\Program Files (x86)\Microsoft Dynamics\GP2018"
$securePassword = Read-Host 'SA password' -AsSecureString
$plainPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
## note: if you're comfortable saving your password in plain text in the file, you can replace the two previous lines with simply $plainPassword = "my password"

##### make sure necessary modules are loaded #####
Import-Module gp2018
Import-Module sqlps

##### back up the source database #####
Write-Host "Backing up source database..."
Backup-SqlDatabase -ServerInstance $serverInstance -Database $sourceDBName -BackupFile $fullBackupFileName -CopyOnly -Initialize

##### check if the destination database already exists
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
$db = $server.databases[$newDBName]
$targetExists = !(!($db))

##### remove the target database if necessary, first noting the file locations #####
if($targetExists) {
    Write-Host "Target database exists. Removing it..."
    $query = Write-Output "select f.name, f.physical_name from sys.databases d inner join sys.master_files f on f.database_id=d.database_id and d.name='$newDBName' order by type"
    $destinationfiles = Invoke-SqlCmd -ServerInstance $serverInstance -Database "master" -Query $query
    $server.KillAllProcesses($newDBName)
    $db.Drop()
}

##### add the new company if necessary and then note the file locations #####
if(!$targetExists) {
    Write-Host "Target database does not yet exist. Creating it..."
    Add-CompanyDatabase -GPClientDirectory $gpClientDirectory -SqlAdmin "sa" -SqlAdminPassword $plainPassword -SqlServerName $serverInstance -CompanyName $newCompanyName -CompanyDbName $newDBName
    $query = Write-Output "select f.name, f.physical_name from sys.databases d inner join sys.master_files f on f.database_id=d.database_id and d.name='$newDBName' order by type"
    $destinationfiles = Invoke-SqlCmd -ServerInstance $serverInstance -Database "master" -Query $query
}

##### overwrite the new company with the source backup #####
$query = Write-Output "select f.name, f.physical_name from sys.databases d inner join sys.master_files f on f.database_id=d.database_id and d.name='$sourceDBName' order by type"
$sourcefiles = Invoke-SqlCmd -ServerInstance $serverInstance -Database "master" -Query $query
$relocateData = New-Object Microsoft.SqlServer.Management.smo.RelocateFile($sourcefiles[0].name, $destinationfiles[0].physical_name)
$relocateLog = New-Object Microsoft.SqlServer.Management.smo.RelocateFile($sourcefiles[1].name, $destinationfiles[1].physical_name)
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
$db = $server.databases[$newDBName]
if($db) { $server.KillAllProcesses($newDBName) }
Write-Host "Overwriting target database with source backup..."
Restore-SqlDatabase -ServerInstance $serverInstance -Database $newDBName -BackupFile $fullBackupFileName -ReplaceDatabase -RelocateFile @($relocateData, $relocateLog)
Write-Host "Correcting INTERID references..."
Invoke-Sqlcmd -InputFile 'c:\scripts\RestoreInterid.sql' -Database $newDBName
Write-Host "Process complete."

Edited by user Thursday, August 1, 2019 10:54:22 AM(UTC)  | Reason: Not specified

Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.