«

»

Print this Post

NAV 2013 R2: Backup and Restore

NAV 2013 R2: Backup and Restore
4 votes, 4.00 avg. rating (82% score)

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…

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

12 comments

4 pings

Skip to comment form

  1. Jan Jaap Smit

    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

  2. Ivan

    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

  3. waldo

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

  4. waldo

    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 .. :-).

  5. Ivan

    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

    1. waldo

      Ivan,
      I think you missed my reply above? :-)

  6. Robert de Roos

    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

    1. waldo

      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!

    2. Robert de Roos

      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

    3. waldo

      Great!
      Must have been something like that ;-)

  7. Tom

    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

    1. waldo

      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..

  1. NAV 2013 R2: Backup and Restore - Waldo's Blog - Microsoft Dynamics NAV

    […] Continue reading » […]

  2. Developers Speed Bump in NAV 2013 R2 | Gunnar's Blog

    […] 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. NAV 2013 R2: Powershell function to check if ServerInstance is Multitenant or not » waldo's blog

    […] 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. NAV 2013 R2: Powershell function to check if ServerInstance is Multitenant or not - Waldo's Blog Microsoft Dynamics NAV - Microsoft Dynamics NAV - Microsoft Dynamics Community

    […] 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