Archive

Posts Tagged ‘copy database’

Copy Production DB to Test/Training DB

January 17th, 2011 1 comment

If you are a Syteline administrator, you will often need to refresh your Test/Training database with update-to-date Production database.

The most common way for copying DBs in SQL server is to use the backup/restore function.

1) Bring down all Infor service.

2) Backup your Syteline production database.

In SQL management studio, right click database name, task-> backup.

image

3) Restore it to Test/Training DB.

In SQL management studio, right click database name, task-> restore –> database.

image

Select source from device, give it the backup file location and file name.

Click Option.  Select the “Overwrite the existing database” option.  Provide the restore to DB

image

4) Once the restore process is done, right click on your Test/Training database name, go “Property –> File”.  Make sure the database owner is sa.  If it is not, change it to sa.

5) Run the below command to your Test/Training database.

ALTER DATABASE dbname SET TRUSTWORTHY ON

6) Bring back all Infor service.

7) Login to your Test/Training Syteline system.  Go to Site/Entities form, change the “Database Name” and “Form Database Name” to correct name.  Since this is a copy of your production database, it carries that Production DB name over, and you will need to change them to correct Test/Training DB name.

image

8) In a multi-site environment, there are a few more steps need to be completed.  First, if you are using intranet licensing, you may have problem login, even with sa account.  Need to do the following in SQL.

1.  Temporarily disable any replication for the Site Admin category in the database to prevent any later updates from replicating over to the production database. This is generally only an issue if the test, pilot, or development database is housed on the same server as production, but I would strongly recommend disabling replication under any circumstances to avoid any possible unexpected issues.

To disable replication for Site Admin, run the following in SQL against the database that has been copied over:

update rep_rule set disable_repl = 1 where category = ‘Site Admin’

2. With replication disabled in step 1, now update the site record to temporarily disable intranet licensing, which should allow the ‘sa’ user to login without error. To disable intranet licensing, run the following query in SQL:

update site set IntranetLicensing = 0

9) Now, you should be able to login with sa account.  Complete the above step 7) to update the DB name.  This need to be done for all sites.

10) If the Site name is differ between your production and pilot environment, you need to run the “Change Site Name” utility to correct the site name.

11) Finally, regen Replication Trigger.

12) Once all the updates are finished, replication triggers are regenerated,  you should be able to return to the Sites/Entities form and re-enable Intranet Licensing for all sites, and then do the same for Disable Replication in the Replication Rules form.

13) Update the license

Done.