NAV 2013 R2: Backup and Restore

Since today, about 25 new “How Do I”-video’s were uploaded to youtube. This means.. video’s that were already available to partners, are now made available for everyone. As mentioned before, I have been participating in some video’s, including the one about Backup/Restore: How Do I: Backup and Restore in a Multitenant Environment in Microsoft Dynamics NAV 2013 R2 .

As you might know, the native backup/Restore tool has been removed from the stack .. plus .. multitenancy brings some new perspectives in this matter. Or should I say “challenges”. In this video, I try to tackle these challenges using Powershell. In this blog, I would like to share the powershell scripts that I used, for your convenience (I guess it’s somewhat easier to copy/paste from a blogpost ;-)). For more info, I recommend you to watch the movie.

Backup

The backup script is actually just backing up the App-db and after that, looping through the tenants, and backup those databases as well.. :

	$ServiceInstance = 'DynamicsNAV71'
	
	#Backup the Application Database
	$NavApplication = Get-NAVApplication $ServiceInstance
	Backup-SqlDatabase -ServerInstance $NavApplication.'Database Server' -Database $NavApplication.'Database Name' -BackupAction Database
	
	#Backup All the Tenant Databases
	$NavTenants = Get-NAVTenant $ServiceInstance
	Foreach ($NavTenant in $NavTenants) {
	    Backup-SqlDatabase -ServerInstance $NavTenant.DatabaseServer -Database $NavTenant.Databasename -BackupAction Database
	}

Restore from a folder

The restore is quite simply done as well.. : loop through the folder where all backups were placed, and restore the backups one-by-one:

	$Backups = dir 'c:\$Restore\*.bak'
	
	Set-NAVServerInstance DynamicsNAV71 -stop
	
	foreach ($BackupFile in $Backups) { 
	    'Restoring ' + $BackupFile.Directory + '\' + $BackupFile.Name
	    
	    $server = New-Object Microsoft.SqlServer.Management.Smo.Server localhost 
	    $backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $BackupFile, "File" 
	    $Restore = New-object Microsoft.SqlServer.Management.Smo.Restore
	
	    #Set properties for Restore 
	    $Restore.NoRecovery = $false; 
	    $Restore.ReplaceDatabase = $true;
	    $Restore.Devices.Add($backupDevice) 
	    $RestoreDetails = $Restore.ReadBackupHeader($server)
	    $Restore.Database = Get-ChildItem $BackupFile | % {$_.BaseName}
	    $Restore.SqlRestore($server)
	}

 

After this script, you have to mount the app and tenants to a multitenant serverinstance (which is basically what I explained in another video (also these scripts will be shared on a later stage)).

What about Backing up and Restoring 1 company

Well, I am planning a blog (or movie) about it, because there are some things to think about: mainly the fact that when you’re restoring a company in a tenant with shared tables (DataPerCompany=No), then those tables are going to be overwritten, and you have the chance of ending up with an inconsistant database. Be careful with this.

This is a great feature, but you have to know what you’re doing… . (and let’s be honest: should there be shared tables in the first place? ;-)). Stay tuned…

4.00 avg. rating (82% score) - 4 votes

Permanent link to this article: https://www.waldo.be/2013/10/18/nav-2013-r2-backup-and-restore/

17 comments

4 pings

Skip to comment form

  1. Hi Waldo,

    Thx for the video’s. One question from my site: How to get a company Data set from NAV2013 into NAV2013R2 since I can make an fbk Export, but that does not exist anymore in R2. Can that only be done by taking the whole SQL DB and then open that company with R2? Or copy it from there via Powershell?

    As a functional consultant I could Always do this myself, but now???

    No customization is done yet to the Original company, BTW

    Best regards,

    Jan Jaap Smit

    • Ivan on October 22, 2013 at 8:56 am
    • Reply

    Hi Waldo,

    This is very helpful indeed. I was wondering is there a way to convert Multi tenancy database back into Single tenancy.

    Thanks

    Kind regards,
    Ivan

  2. Jan Jaap,
    Well .. all I know is they’re working on it ;-).

  3. Ivan,
    there is (or at least should be) a solution. I never tried it, but in fact, it’s exactly how the upgrade from 2013 works: convert to multitenancy and at the end convert back to single tenancy. I advise you to have a look at that video .. :-).

    • Ivan on October 22, 2013 at 9:40 am
    • Reply

    Hi Waldo,

    Converting multi company NAV 2013 into multi tenancy NAV 2013 R2 works very well. Thanks to your video I managed to extract all the scripts needed to do this.
    So now I have NAV 2013 R2 database in Multi tenancy. How do I convert it back into Single tenancy ?
    In multi tenancy the data is in one (or more) database(s) and application is in another database.
    How do I merge this back now to be in one database. I do not see any cmdlet’s to do this 🙁

    Thanks.
    Ivan

      • waldo on October 22, 2013 at 9:42 am
        Author

      Ivan,
      I think you missed my reply above? 🙂

  4. Hi Waldo,

    First of all thank you for your good demo video’s!

    I have followed your script on creating a multi tenant database from a single database with multiple companies. I used the NAV NA demo database (North America) for this.
    However, when I try to mount the first tenant (after I separated the objects from the data) I get an error.
    I have also tried to do this step manually from the Microsoft Dynamics NAV 2013 R2 Administration but I get the same error.

    Mount-NAVTenant : The following SQL error was unexpected.
    User does not have permission to alter database ‘NAVNAPF2013R2_Staging’, the database does not exist, or the database is not in a state that allows acces
    s checks.
    ALTER DATABASE statement failed.

    In the eventviewer:

    ALTER DATABASE statement failed.
    SQL statement:
    ALTER DATABASE [NAVNAPF2013R2_Staging] SET SINGLE_USER WITH NO_WAIT

    If I set the database in single user mode with SQL server management studio first and then run the mount-NAVTenant command does not help and still ends with the same error.

    I have done this on my local SQL server installation and no other users have access.
    For this purpose I restored a backup of the demo database and created a new NAV instance.

    Do you have any idea how to solve this issue?
    Should I report this to Microsoft?

    Best regards,
    Robert

      • waldo on December 27, 2013 at 3:47 pm
        Author

      Hi Robert,

      This seems an awkward error. Never had this before … . I would definitely try to log it at Microsoft.

      A few things that com to mind:
      – is it a test-environment?
      – Did you have the database open in some client (SSMS, mounted to a different service tier)
      – Did you do the steps in the right sequence?

      If you’re not able to work yourself through .. just let me know .. I’ll make my script available!

    1. Hi Waldo,

      Thanks for your reply.

      I have found the cause of the problem.
      The error was caused due to insufficient rights of the NT AUTHORITY\NETWORK SERVICE login on SQL server

      Best regards,
      Robert

      • waldo on January 2, 2014 at 7:40 am
        Author

      Great!
      Must have been something like that 😉

    • Tom on April 17, 2014 at 2:53 pm
    • Reply

    Hi Waldo,

    Thanks for the video.

    Does this require SQL server and NST in same server or NST and SQL server can be separate server.

    Best Regards
    Tom

      • waldo on April 17, 2014 at 3:42 pm
        Author

      I didn’t test it, but it should not be a problem. You need the NAV-commandlets and the SQL commandlets. If you have them, I would suggest to execute it from the NAV Server, and provide the necessary connection details..

    • Anders on March 31, 2015 at 9:56 am
    • Reply

    Hi Waldo

    I got an issue with the Backup-SqlDatabase -ServerInstance $NavTenant.DatabaseServer -Database $Nav…..aso.. line
    In my case the SQL service is on one server and the NAV service is on another server
    I’m running the script from the NAV server
    so i figured out that i needed to use the invoke-command to send the sql backup request to the sql server
    Invoke-Command {Backup-SqlDatabase -ServerInstance $NavTenant.DatabaseServer -Database $Nav….aso..
    but when i send the command (wich includes parameters / varibles) it does not send the variable value but the variable name insted and that variable doesnt exsist on the sql server so i get an error

    this might be a general PS question … but do you know how to paste the value of an variable in to the parameter when using the invoke-command ??

    Nice Blog by the way ! 😉

    Best Regards
    Anders

      • waldo on April 1, 2015 at 10:17 am
        Author

      I’m not able to see what is going wrong .. I’m not getting the error. Then again, I don’t have a test environment at my disposal where I have the NST separated from the database.

      I never used the invoke-command, but testing it, it seems to just take my variables correctly – no unexpected behaviour like you describe.

      Furthermore, I don’t think you need it. Isn’t it possibel to just provide a decent “serverinstance”, providing the the server and instance from your database server?

      • Anders on April 1, 2015 at 11:41 am

      Hi Waldo .. thanks for the reply … 🙂

      Its difficult to test if both sql and NAV services are installed on the same server …
      because then the Nav commands are avallible on the sql server and sql commands are avalible for NAV cmdlets
      but when you run it remotely the backup-sqldatabase command wont be avallible on th eNAV server (thats why i use the invoke-command) but as you can see in the error … it sends a null value insted of the accually varibles value ….i think it is because that it sends the string to the sql server and executes ít there … but on the sql server the varible dosent exsist .. and is therefor null .. but is there a trick for sending the value insted of the varible … ?

      **********

      PS C:\Windows\system32>
      Set-ExecutionPolicy unrestricted -Force
      Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\80\Service_NAV2015_MT\NAV2015_MT\NavAdminTool.ps1’
      $ServiceInstance = ‘NAV2015_MT’
      $sqlserver = ‘sql-d166′
      #Backup the Application Database
      $NavApplication = Get-NAVApplication $ServiceInstance
      Invoke-Command -ComputerName $sqlserver {Backup-SqlDatabase -ServerInstance $NavApplication.’Database Server’ -Database $NavApplication.’Database Name’ -BackupAction Database}
      #Backup All the Tenant Databases
      $NavTenants = Get-NAVTenant $ServiceInstance
      Foreach ($NavTenant in $NavTenants)

      {
      Invoke-Command -ComputerName $sqlserver {Backup-SqlDatabase -ServerInstance $NavTenant.DatabaseServer -Database $NavTenant.Databasename -BackupAction Database}
      }

      ******then i get these errors********

      Cannot validate argument on parameter ‘ServerInstance’. The argument is null or empty. Provide an argument that is not null or empty, and then try the
      command again.
      + CategoryInfo : InvalidData: (:) [Backup-SqlDatabase], ParameterBindingValidationException
      + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand
      + PSComputerName : sql-d166

      Cannot validate argument on parameter ‘ServerInstance’. The argument is null or empty. Provide an argument that is not null or empty, and then try the
      command again.
      + CategoryInfo : InvalidData: (:) [Backup-SqlDatabase], ParameterBindingValidationException
      + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand
      + PSComputerName : sql-d166

      Cannot validate argument on parameter ‘ServerInstance’. The argument is null or empty. Provide an argument that is not null or empty, and then try the
      command again.
      + CategoryInfo : InvalidData: (:) [Backup-SqlDatabase], ParameterBindingValidationException
      + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand
      + PSComputerName : sql-d166

      ************************************

      Best regards

      Anders

    • Adil Rasheed on July 10, 2023 at 2:40 pm
    • Reply

    Hi,

    Can you please share video link again. The one specified in the post are not working.

      • waldo on July 10, 2023 at 2:57 pm
        Author

      Microsoft took it down as it was hopelessly old .. but I did find a copy here: https://www.youtube.com/watch?v=1AI2t_GdoOc

  1. […] Continue reading » […]

  2. […] No more FBK files.  You will now use SQL or PowerShell to do backup and restore.  Take a look at Waldo’s website for details.  Microsoft is planning to supply a new and easy way for Backup and Restore as Mark […]

  3. […] working on improving the Backup/Restore procedure from this blogpost .. . While doing that, I was looking for a way to see whether my current instance was actually a […]

  4. […] working on improving the Backup/Restore procedure from this blogpost .. . While doing that, I was looking for a way to see whether my current instance was actually a […]

Leave a Reply to Anders Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.