Tip of the Week #41 – Quick Database Cleanup

Just in time for the holidays, developer Peter Tijsma brings us a tip about being mindful of needless clutter.

Tip of the Week #41 – Quick Database Cleanup

We’ve all been there – having a test database where multiple users are testing and creating new companies on the fly – and never deleting them.

At a certain point you end up with a huge, unmanageable database that no longer performs at all.

Ideally users would think to delete their test companies when finished – unfortunately this rarely happens.

So the official way to delete a company in NAV2013 R2 has been to go here:

Select the company and press “Delete

However, even for a single company this method takes considerable processing time. So just imagine the time required for 20 test companies.

When doing release work, we encounter the same situation: We want to make sure our release master only includes the companies that we want to keep.

Since we’re trying to automate as much as possible, the slow procedure of manually selecting and deleting companies would be rather contrary to our own methodologies; hence this tip of the week is a taste of our own medicine.

The following script removes ALL companies from a database – except the ones we want to keep.

$InstanceName = “DynamicsNAV90”

$ObsoleteCompanies = Get-NAVCompany -ServerInstance $InstanceName | Where {$_.CompanyName -ne “PrintVis Unlimited US” -and $_.CompanyName -ne “PrintVis Unlimited”}

foreach ($company in $ObsoleteCompanies) {

$companyName = $company.CompanyName

Write-Host “Removing obsolete company$companyName …” -ForegroundColor DarkYellow

Remove-NAVCompany -ServerInstance $InstanceName -CompanyName $companyName -Force

}

Replace:

  • “DynamicsNAV90”
    with the NAV Service-Tier Name of the NST connected to the correct database
  • “PrintVis Unlimited US” & “PrintVis Unlimited”
    with the name(s) of the companies you want to keep in the database

 

Execute the script and voila, all companies are deleted except the ones you wanted to keep!

 

Thank you Peter!

Call Now Button