Skip to Content

How to create a copy of an active database

The following instructions were originally created by Henk Griesshaber (SAP?).
They are merely mirrored here to spread the information.

background

In the new versions of SAP Business One it is not possible to transfer company data from one company to another (from within Business One).

Yet you might need to transfer a database for a number of reasons:
– to create a backup of a live system
– to create a developing system
– to transfer changes from development into a live system

Procedure

By far the fastest and safest way to copy a database is to use the backup and restore feature of the Enterprise Manager (SQL Server 2000 Client Component).

Example: Creating a copy of the standard Database SBO_DemoUS

1. Create a backup

image

Create a backup of the desired database…using the backup database function, of course ๐Ÿ˜‰

image

Leave the default settings and add a destination where you want to save the backup.

Define the name of the backup and select OK.

The destination folder and file name has now been added.

Select OK.

This starts the back-up process.

2. Restore database

image

Restore this database, but assign it to a new database name (!).
This creates a new database as copy of the original database

image

Select Restore from the database folder.

In the Restore as Database field, enter a new (!) name.

In the Show backups of database field, select the original database from which you would like to see the backups.

image

Select the database to be restored.

Then select OK to start the restore process.

Result

image

A second (new) database is created as copy of the original database.

It is automatically attached to the server and will appear in SAP Business One (need to scan again!).

Important Note

If you have trouble accessing your newly created database, note that SQL Server does not /cannot create exact copies of your database users.

Deleting and re-creating the users will allow SAP Business One to access the database.

All due credits to Henk Griesshaber.

Henk I could not contact you and this was just too good to be left sleeping on my workstation.

Lutz Morrien

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

    1. Former Member Post author
      Hi Oki,
      good point. I guess that the SBO version of the target SAP Business One system should be higher or equal to the database version being copied. Copying an inferior version database into a superior version SBO should result in an upgrade… unless there are database fields added to each database during the version upgrade.

      To be on the save side, one should consider copying databases between equal versions, I guess.

      Lutz Morrien

      (0) 
  1. Former Member
    Hi,

    We have used the following method for a long time. We have found it much less awkward than restoring .bak files:

    1) In the Enterprise Manager, right click the database you wish to copy. Select All tasks-detach database.

    2) Go to the folder where your database files are located. Simply make a copy of the %dbname%.mdf and %dbname%.ldf files and rename them.

    3) In the Enterprise Manager, right-click your database server. Take All tasks-Attach Database.
    Pick the .mdf file for the database you just detached. Make sure that you have the correct database owner (sa) and click Ok.

    4) Repeat step 3 for the newly copied mdf file. This time, you should specify a new name in the “Attach as” field.

    Naturally, step 4 could be executed on another database server. This procedure supports different SBO versions, simply remember that the target environment’s SBO-Common file must be at least on the same level (or newer) as the company database is (A lower-level database will be upgraded once you log into it in the new environment).

    Regards,
    Henry Nordstrom

    (0) 
  2. Former Member
    Luc,

    This is an easy way of copying a database but I thibk when you do that it does not copy the transactions which could be annoying.

    Can someone confirm that?

    Vincent

    (0) 

Leave a Reply