Change Interface Name After DB Restore

%3CLINGO-SUB%20id%3D%22lingo-sub-2323124%22%20slang%3D%22en-US%22%3EChange%20Interface%20Name%20After%20DB%20Restore%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2323124%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20Job%20in%20MSSQL%20Management%20Studio%202016%20to%20restore%20a%20copy%20of%20production%20every%20night%20from%20the%20backup%20to%20have%20a%20troubleshooting%20environment%20for%20Microsoft%20Dynamics%20NAV%202018.%3C%2FP%3E%3CP%3EIt%20is%20currently%20working%20except%20for%20updating%20the%20name%20of%20the%20environment%20after%20restore%20to%20%22ICD-ProdCopy%22%20and%20not%20%22ICD-Live%22.%20I%20can't%20get%20it%20to%20update.%20Any%20suggestions%3F%3C%2FP%3E%3CP%3EHere%20is%20my%20code%3A%3C%2FP%3E%3CP%3EALTER%20DATABASE%20%5BICD-ProdCopy%5D%20SET%20Single_User%20WITH%20Rollback%20Immediate%3C%2FP%3E%3CP%3EDrop%20table%20%23BackUpFilesAvailable%3C%2FP%3E%3CP%3EDeclare%20%40LatestBackupFile%20varChar(255)%3CBR%20%2F%3EDeclare%20%40BackUpFolder%20varchar(255)%3C%2FP%3E%3CP%3Eset%20%40LatestBackupFile%20%3D%20null%3CBR%20%2F%3Eset%20%40BackUpFolder%20%3D%20'%5C%5Cicdknavsql01%5CG%5CMSSQL%5CBACKUPS%5CICD-LIVE%5C'%3C%2FP%3E%3CP%3Ecreate%20table%20%23BackUpFilesAvailable%20(%3CBR%20%2F%3EFileName%20varchar(255)%2C%3CBR%20%2F%3Elevels%20int%2C%3CBR%20%2F%3Efiles%20int)%3C%2FP%3E%3CP%3E--get%20all%20the%20files%20and%20folders%20in%20the%20backup%20folder%20and%20put%20them%20in%20temporary%20table%3CBR%20%2F%3Einsert%20into%20%23BackUpFilesAvailable%20exec%20xp_dirtree%20%40BackUpFolder%2C0%2C1%3C%2FP%3E%3CP%3E--get%20the%20latest%20backup%20file%20name%3CBR%20%2F%3Eselect%20top%201%20%40LatestBackupFile%20%3D%20%40BackUpFolder%20%2B%20FileName%20from%20%23BackUpFilesAvailable%20where%20Filename%20like%20'%25.bak'%20order%20by%20filename%20desc%3C%2FP%3E%3CP%3E--execute%20the%20restore%3CBR%20%2F%3ERESTORE%20DATABASE%20%5BICD-ProdCopy%5D%20FROM%20DISK%20%3D%20%40LatestBackupFile%20WITH%3C%2FP%3E%3CP%3EMOVE%20N'Demo%20Database%20NAV%20(11-0)_Data'%20TO%20N'E%3A%5CMSSQL%5CDATA%5CICD-LIVE.mdf'%2C%3CBR%20%2F%3EMOVE%20N'Demo%20Database%20NAV%20(11-0)_Log'%20TO%20N'F%3A%5CMSSQL%5CLOGS%5CICD-LIVE.ldf'%2C%20REPLACE%3C%2FP%3E%3CP%3EUpdate%20%5BIndependence%20Contract%20Drilling%24Company%20Information%5D%3CBR%20%2F%3ESet%20%5BCustom%20System%20Indicator%20Text%5D%20%3D%20'ICD-ProdCopy'%2C%20%5BSystem%20Indicator%5D%20%3D%20'1'%2C%20%5BSystem%20Indicator%20Style%5D%20%3D%20'1'%3C%2FP%3E%3CP%3EALTER%20DATABASE%20%5BICD-ProdCopy%5D%20SET%20Multi_User%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2324803%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20Interface%20Name%20After%20DB%20Restore%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324803%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043913%22%20target%3D%22_blank%22%3E%40vcarr001%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EI%20can't%20get%20it%20to%20update.%20Any%20suggestions%3F%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3ESorry%2C%20but%20for%20me%20it's%20not%20clear%2C%20what%20you%20mean%3F%20May%20can%20you%20explain%20it%20a%20bit%20more%20detailed%2C%20please%3F%3C%2FP%3E%3CP%3EDo%20you%20get%20an%20error%20message%20or%20...%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOlaf%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

3 Replies

@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

@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'


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