Skip to content

NAV 2013 R2: How to Back up a Live Database and Refresh a Test Database

ArcherPoint How-To Blog: Step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV

If you have a test database, at some point in time you will be asked to refresh this with the current live data, especially when testing. I am often asked how to copy the live database into the test database so accurate testing of a development request can be done.

In this blog, you will learn how to make a backup of the live database and restore this data to an existing test database.

The following steps take you through the Backup and Restore process step by step.

Stopping the Test Database Service

1. Go to your Service Tier computer.

2. Open the “Microsoft Dynamics NAV 2013 R2 Administration” tool.

View from Start button on tool bar

Figure 1. View from Start button on tool bar

3. You should see a screen similar to the following:

Microsoft Dynamics NAV 2013 R2 Administration

Figure 2. Microsoft Dynamics NAV 2013 R2 Administration

4. Click the + next to the Microsoft Dynamics NAV (local).

5. You should now see the option to stop the service in the left hand panel.

Microsoft Dynamics NAV 2013 R2 Administrations Action Bar

Figure 3. Microsoft Dynamics NAV 2013 R2 Administrations Action Bar

6. Stop the service

Creating a SQL Backup

NOTE: If you already have a backup, skip these steps.

1. Go to the NAV2013 SQL Server.

2. Open SQL Server Management Studio.

View from Start button on tool bar

Figure 4. View from Start button on tool bar

3. You should see a screen similar to the following:

SQL Menu

Figure 5. SQL Menu

4. Click the + next to the Databases.

SQL Menu

Figure 6. SQL Menu

5. You now see a list of all available databases.

6. Right-click the database if you want to make a backup.

7. Select Tasks and Backup.

SQL Database List

Figure 7. SQL Database List

8. You will see a screen similar to the following:

SQL Backup Tool

Figure 8. SQL Backup Tool

9. Make sure you select the options you see in Figure 8: Backup type = full, Destination = Disk

10. Click Add.

11. Select the location to which you want to save your backup. Remember, you need to have enough space to complete this task.

NOTE: If you are backing up your live database, this will cause table locking errors until the backup is completed.

12. Click the options tab and verify that the following settings are selected:

SQL Backup Tool

Figure 9. SQL Backup Tool

Restoring the Backup

1. Go to the NAV2013 Server.

2. Open SQL Server Management Studio.

View from Start button on tool bar

Figure 10. View from Start button on tool bar

3. You should see a screen similar to the following:

SQL Menu

Figure 11. SQL Menu

4. Click the + next to the Databases.

SQL Menu

Figure 12. SQL Menu

5. You now see a list of all available databases.

6. Right-click the database you want to restore.

7. Select Tasks and Restore and Files and File groups.

SQL Menu

Figure 13. SQL Menu

8. You should see a screen similar to the following:

SQL Restore Tool

Figure 14. SQL Restore Tool

9. Verify that the To Database is the Test Database.

10. Select the From Device Radio Button.

11. Hit the Ellipsis.

12. Select the location of the backup file you want to restore.

File Location tool

Figure 15. File Location tool

13. Click OK

14. You should now see a screen similar to the following:

SQL Restore Tool

Figure 16. SQL Restore Tool

15. Check the box in the Select the backup sets to restore.

16. Click the Options tab.

17. Verify that the Restore Database Files are named the same as your test Database and test log names; if needed, correct he names.

SQL Restore Tool File Names

Figure 17. SQL Restore Tool File Names

18. Click the Overwrite the existing database (WITH REPLACE).

19. Click OK, and the backup will restore over your existing test Database with the new data.

Starting the Test Database Service

1. Go to your test computer.

2. Open the “Microsoft Dynamics NAV 2013 R2 Administration” tool.

View from Start button on tool bar

Figure 18. View from Start button on tool bar

3. You should see a screen similar to the following:

Microsoft Dynamics NAV 2013 R2 Administration

Figure 19. Microsoft Dynamics NAV 2013 R2 Administration

4. Click the + next to the Microsoft Dynamics NAV (local).

5. You should now see the option to Start the service in the left hand panel.

Microsoft Dynamics NAV 2013 R2 Administration’s Action Bar

Figure 20. Microsoft Dynamics NAV 2013 R2 Administration’s Action Bar

6. Start the service.

7. You should now be able to run your test Database.

 

If you have any questions about this process or other technical questions about Dynamics NAV, please contact ArcherPoint.

Read more "How To" blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.

Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube
Get Help Now