In my job, I have to do a lot of backup and restore of NAV companies in order to create new companies similar to previous company. I am planning to build a .net application to do the job. Basically automate the repetitive stuff, but the problem is the Navision we use is 2009 R2 and I can't find a way to backup and restore a NAV database/company in 2009 R2 using .Net/SQL. Is there any way do this?
As said, there is no way to automate it using script. When performing backup/restore Nav doing many things aside from just create another table set. It creates keys/views, append records to system tables like Company (where list of companies is stored).
From your question I can't understand why do you need backup company to create similar one. Because after that you would have to clear all ledgers etc. Why copy data just to wipe it after all?
Alternative approach you could use to solve problem of creating new company fast is to create a codeunit in Nav that will populate newly created company with all data you need. Take a look at codeunit 2 Company-Initialize. When run it creates empty records in all important setup tables and fills report selection. You can modify it or create similar one that will fill setup tables by your default values or copy them from another company you provide as parameter (use changecompany for that).
Here is one more thing that I've found:
In earlier versions of Microsoft Dynamics NAV, you could create a table by using the INSERT Function (Record) to add a record to table 2000000006, the Company table. In Microsoft Dynamics NAV 2013, it is not supported to create a company by using the INSERT function. You must create companies by using the New Company window in the development environment.
That means that in your version you can even create new Company automaticaly from codeunit I've mentioned.
Also since Nav 2013 R2 there is new capabilities. You can use comandline parameters of finsql.exe to create company (or). And then invoke Nav codeunit from PowerShell script to populate it with data.