Change Interface Name After DB Restore

[vcarr001]

I have created a Job in MSSQL Management Studio 2016 to restore a copy of production every night from the backup to have a troubleshooting environment for Microsoft Dynamics NAV 2018.

It is currently working except for updating the name of the environment after restore to "ICD-ProdCopy" and not "ICD-Live". I can't get it to update. Any suggestions?

Here is my code:

ALTER DATABASE [ICD-ProdCopy] SET Single_User WITH Rollback Immediate

Drop table #BackUpFilesAvailable

Declare @LatestBackupFile varChar(255)
Declare @BackUpFolder varchar(255)

set @LatestBackupFile = null
set @BackUpFolder = '\\icdknavsql01\G\MSSQL\BACKUPS\ICD-LIVE\'

create table #BackUpFilesAvailable (
FileName varchar(255),
levels int,
files int)

--get all the files and folders in the backup folder and put them in temporary table
insert into #BackUpFilesAvailable exec xp_dirtree @BackUpFolder,0,1

--get the latest backup file name
select top 1 @LatestBackupFile = @BackUpFolder + FileName from #BackUpFilesAvailable where Filename like '%.bak' order by filename desc

--execute the restore
RESTORE DATABASE [ICD-ProdCopy] FROM DISK = @LatestBackupFile WITH

MOVE N'Demo Database NAV (11-0)_Data' TO N'E:\MSSQL\DATA\ICD-LIVE.mdf',
MOVE N'Demo Database NAV (11-0)_Log' TO N'F:\MSSQL\LOGS\ICD-LIVE.ldf', REPLACE

Update [Independence Contract Drilling$Company Information]
Set [Custom System Indicator Text] = 'ICD-ProdCopy', [System Indicator] = '1', [System Indicator Style] = '1'

ALTER DATABASE [ICD-ProdCopy] SET Multi_User

[#2]

---

@vcarr001 wrote:

I can't get it to update. Any suggestions?

---

Sorry, but for me it's not clear, what you mean? May can you explain it a bit more detailed, please?

Do you get an error message or ...?

Olaf

[#3]

@olafhelper When you open the production environment on ERP interface, it shows "ICD-LIVE". This means that you are in the production environment. My restore process is creating a production copy for troubleshooting purposes daily. So, this environment refreshes everyday and when that happens I want it to show up as "ICD-ProdCopy" in the ERP interface and not "ICD-LIVE". The code highlighted below is supposed to make that happen, but it's not working.

ALTER DATABASE [ICD-ProdCopy] SET Single_User WITH Rollback Immediate

Drop table #BackUpFilesAvailable

Declare @LatestBackupFile varChar(255)
Declare @BackUpFolder varchar(255)

set @LatestBackupFile = null
set @BackUpFolder = '\\icdknavsql01\G\MSSQL\BACKUPS\ICD-LIVE\'

create table #BackUpFilesAvailable (
FileName varchar(255),
levels int,
files int)

--get all the files and folders in the backup folder and put them in temporary table
insert into #BackUpFilesAvailable exec xp_dirtree @BackUpFolder,0,1

--get the latest backup file name
select top 1 @LatestBackupFile = @BackUpFolder + FileName from #BackUpFilesAvailable where Filename like '%.bak' order by filename desc

--execute the restore
RESTORE DATABASE [ICD-ProdCopy] FROM DISK = @LatestBackupFile WITH

MOVE N'Demo Database NAV (11-0)_Data' TO N'E:\MSSQL\DATA\ICD-LIVE.mdf',
MOVE N'Demo Database NAV (11-0)_Log' TO N'F:\MSSQL\LOGS\ICD-LIVE.ldf', REPLACE

ALTER DATABASE [ICD-ProdCopy] SET Multi_User

--Update the name to "ICD-ProdCopy" when you open NAV
Update [Independence Contract Drilling$Company Information]
Set [Custom System Indicator Text] = 'ICD-ProdCopy', [System Indicator] = '1', [System Indicator Style] = '1'

[#4]

---

I want it to show up as "ICD-ProdCopy" in the ERP interface and not "ICD-LIVE".

---

Sorry, I don't know about Dynamic NAV, so I can't help.

Olaf