Clever way to backup the objects from multiple databases?

pdjpdj Member Posts: 643
edited 2014-03-06 in SQL General
We have many customer NAV databases in versions from NAV4 to 2013R2 in our SQL database for development purposes. Currently we daily take a full backup of all databases, because that is the only place we got the objects. However; the total size of the databases are soon causing problems for our backup device. We would therefore like to ignore the actual NAV data of the databases – we actually only need to be able to restore the NAV objects. All developers are allowed to create new databases, so it should be possible for the daily backup to automatically include new databases without any additional configuration.

But is that possible? We have currently considered:

* Only backup the file group with the MDF-file, but is it actually possible to build a new NAV database from only an MDF-file?
* Make T-SQL to export the Object table from all databases, but can that be used to create a new database?
* Switch to a version controls system like TFS to separate the NAV objects from the database. However; we are quite happy with our current much disciplined way of developing and we are using Object Manager in most databases.
* We even considered automating an old FBK objects-only backup using ExpandIT Backup Utility, but from 2013R2 that’s a no-go.

Anyone with a good solution?
Regards
Peter

Comments

  • ara3nara3n Member Posts: 9,249
    create a separate empty nav db for each database and setup sql replication for the object table. backup the empty nav db.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • pdjpdj Member Posts: 643
    Hi ara3n,

    Do you know if that is possible?
    I would expect the NAV db to be corrupt, when the objects differ from the SQL tables.

    I also consider a solution that copies each database, and then drop all tables with $ in the name and empty the Company table. Then a new backup of this database will be tiny.
    Regards
    Peter
  • pdjpdj Member Posts: 643
    pdj wrote:
    I also consider a solution that copies each database, and then drop all tables with $ in the name and empty the Company table. Then a new backup of this database will be tiny.
    Hey! It could actually be even simpler:

    Loop all database:
    - Create a SQL Snapshot (We are using the Developer edition)
    - Drop tables with $ in name
    - Empty the Company table
    - Full backup of database
    - Revert to Snapshot

    Anyone see issues with this approach?
    Regards
    Peter
  • ara3nara3n Member Posts: 9,249
    you would not create any company in the empty database so there is no tables on sql. Also it is only used to export the objects as backup.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.