I just finished my install on databases and service to our developement server. When creating several databases and several instances I wanted to use PowerShell.
I based the setup on the demo database backup and used PowerShell ISE as an Administrator to do the setup.
First step is to set up parameters.
#Change Id to match the solution type
$Id = ‘GL’
#Change ClientPort to available port
$ClientPort = 9213
Then I run the script.
[code]#Change Id to match the solution type
$Id = ‘GL’
#Change ClientPort to available port
$ClientPort = 9213
#Database Server Name
$servername = ‘localhost’
#Location of Demo Database Backup File
$backupFile = ‘T:\Demo Database NAV (7-1).bak’
#New locations for Database Files
$DataOrigName = ‘Demo Database NAV (7-1)_Data’
$DataDestName = ‘M:\SQLdata\mdf\NAV71_’ + $Id + ‘.mdf’
$LogOrigName = ‘Demo Database NAV (7-1)_Log’
$LogDestName = ‘L:\SQLdata\ldf\NAV71_’ + $Id + ‘.ldf’
#New Database Name
$DatabaseName = ‘NAV71_’ + $Id
#New Service Instance Name
$ServiceInstanceName = ‘NAV71_’ + $Id
#New Company Name
$NewCompanyName = ‘CRONUS ‘ + $Id + ‘ 2013 R2’
#Other Ports to use
$SoapPort = $ClientPort + 1
$ODataPort = $SoapPort + 1
$MgtPort = $ODataPort + 1
#Database and Table with the Developement License
$CopyLicenseFromDB = ‘NAV71_AdvaniaStarter’
$tblName = ‘$ndo$dbproperty’
#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null
#load server, backup, and restore objects
$server = new-object("Microsoft.SqlServer.Management.Smo.Server") $servername
$dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
#settings for the restore
$dbRestore.Action = "Database"
$dbRestore.NoRecovery = $false;
$dbRestore.ReplaceDatabase = $false;
$dbRestorePercentCompleteNotification = 5;
$dbRestore.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
#get the db name
$dbRestoreDetails = $dbRestore.ReadBackupHeader($server)
#print database name
"Database Name from Backup File : " + $dbRestoreDetails.Rows[0]["DatabaseName"]
#give a new database name
$dbRestore.Database = $DatabaseName
#specify new data and log files (mdf and ldf)
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
#set file names; use the default database directory
$dbRestoreFile.LogicalFileName = $DataOrigName
$dbRestoreFile.PhysicalFileName = $DataDestName
$dbRestoreLog.LogicalFileName = $LogOrigName
$dbRestoreLog.PhysicalFileName = $LogDestName
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)
#execute the restore!
$dbRestore.SqlRestore($server)
write-host "Restore of " $DatabaseName "Complete"
$database = $server.Databases[$DatabaseName]
# grant access to database
$name = ‘NT AUTHORITY\NETWORK SERVICE’
$user = new-object (‘Microsoft.SqlServer.Management.Smo.User’) $database, $name
$user.Login = ‘NT AUTHORITY\NETWORK SERVICE’
$user.DefaultSchema = ‘dbo’
$user.Create()
# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
# Copy License
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=master");
$cn.Open()
$q = "UPDATE [$DatabaseName].[dbo].[$tblName] SET [license] = (SELECT [license] FROM [$CopyLicenseFromDB].[dbo].[$tblName])"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
New-NAVServerInstance -ServerInstance $ServiceInstanceName -ClientServicesCredentialType Windows -ClientServicesPort $ClientPort -DatabaseName $DatabaseName -DatabaseServer Localhost -ManagementServicesPort $MgtPort -SOAPServicesPort $SoapPort -ODataServicesPort $ODataPort
Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Start
Rename-NAVCompany -ServerInstance $ServiceInstanceName -CompanyName ‘CRONUS Ísland hf.’ -NewCompanyName $NewCompanyName
Get-NAVCompany -ServerInstance $ServiceInstanceName
# give developers SUPER access to the database
$grpNames = (‘sec_Dynamics_NAV_Dev1’, ‘sec_Dynamics_NAV_Dev2′)
foreach ($grp in $grpNames)
{
Get-ADGroupMember -Identity $grp -Server "172.16.1.2" | foreach {
New-NAVServerUser -ServerInstance $ServiceInstanceName -Sid $($_.SID) -FullName $($_.name) -LicenseType Full -State Enabled
New-NAVServerUserPermissionSet -ServerInstance $ServiceInstanceName -Sid $($_.SID) -PermissionSetId "SUPER"
}
# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’" + "SKYRR\" + $grp + "’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
}
[/code]
First step is to initialize all variables.
- Set variable for the location of the Demo Database Backup File
- Set variables for new location of Database Files
- Set variable for new Database Name
- Set variable for new Service Instance Name
- Set variable for new Company Name
Next step is to restore the database.
- Setup SMO environment
- Setup the restore
- Get current Database Name
- Set new name for the Database
- Set new locations for the Database Files
- Execute Restore
The user running the NAV Server Instance will need to have access to the new database.
- Create new user for ‘NT AUTHORITY\NETWORK SERVICE’
- Adds role ‘do_owner’ to user ‘NT AUTHORITY\NETWORK SERVICE’
Before starting the NAV Server Instance I will copy the developement license to the new database
- Setup SQL connection to the master database
- Prepare SQL statement to copy the license from another database
- Execute SQL statement
New NAV Server Instance is created and started. The existing company is renamed to the new company.
Use Active Directory to find all users in the developement group and add them as NAV users and give them SUPER permission. The groups are also given ‘db_owner’ permission for the database to be able to run Developement Environment.
If the Active Directory PowerShell commands are not installed just activate that feature or look here for further details.
