Search

Index

Revindex Storefront

How to migrate product data

Last updated on 2022-07-26 3 mins. to read

If you run multiple environments such as a test and a production environment, you can follow the suggested approach to copy your product data from production (source) to your test (target) environment.

Please ensure to take full backup first before starting the migration. This is an advanced topic and should only be performed by an experienced administrator who has strong understanding of DNN, SQL database and Revindex Storefront. The information provided here is to be used at your own risk without any warranty or support.

The following assumptions are required to successfully refresh the product data:

  • Both environments operate the same software version
  • Both environments have the same number of portals and Portal ID numbers.
  • You don't require retaining sales orders, product reviews, voucher history data in the target database.
  • You are not interested to refresh the Users data.

In order to refresh the product tables, the general idea is to delete the data at the target database first followed by inserting back from the source database. Because many adjacent tables rely on the data from these tables that are being refreshed, we also need to delete the data from the adjacent tables to maintain integrity. For example, we need to delete the data from the Revindex_Storefront_ProductReview,  Revindex_Storefront_SalesOrderDetail, etc.

Using SQL Server Management Studio:

  1. Execute the following SQL statements to delete data in your target database:

    DELETE FROM [dbo].[Revindex_Storefront_FundHistory]
    DELETE FROM [dbo].[Revindex_Storefront_Fund]
    DELETE FROM [dbo].[Revindex_Storefront_Favorite]
    DELETE FROM [dbo].[Revindex_Storefront_SalesReturnDetail]
    DELETE FROM [dbo].[Revindex_Storefront_SalesReturn]
    DELETE FROM [dbo].[Revindex_Storefront_Right]
    DELETE FROM [dbo].[Revindex_Storefront_ProductChannel]
    DELETE FROM [dbo].[Revindex_Storefront_CrosssellProduct]
    DELETE FROM [dbo].[Revindex_Storefront_AddressValidationMethod]
    DELETE FROM [dbo].[Revindex_Storefront_RewardsPointHistory]
    DELETE FROM [dbo].[Revindex_Storefront_RewardsPoint]
    DELETE FROM [dbo].[Revindex_Storefront_ProductVariantOption]
    DELETE FROM [dbo].[Revindex_Storefront_ProductVariantGroupOption]
    DELETE FROM [dbo].[Revindex_Storefront_ProductVariantGroup]
    DELETE FROM [dbo].[Revindex_Storefront_VoucherHistory]
    DELETE FROM [dbo].[Revindex_Storefront_Voucher]
    DELETE FROM [dbo].[Revindex_Storefront_WishListDetail]
    DELETE FROM [dbo].[Revindex_Storefront_ProductAttribute]
    DELETE FROM [dbo].[Revindex_Storefront_ProductAttributeDefinitionSelection]
    DELETE FROM [dbo].[Revindex_Storefront_ProductAttributeDefinition]
    DELETE FROM [dbo].[Revindex_Storefront_ProductAttributeGroup]
    DELETE FROM [dbo].[Revindex_Storefront_Gallery]
    DELETE FROM [dbo].[Revindex_Storefront_RelatedProduct]
    DELETE FROM [dbo].[Revindex_Storefront_RequiredProduct]
    DELETE FROM [dbo].[Revindex_Storefront_SalesOrderDetail]
    DELETE FROM [dbo].[Revindex_Storefront_RecurringSalesOrder]
    DELETE FROM [dbo].[Revindex_Storefront_ProductCategory]
    DELETE FROM [dbo].[Revindex_Storefront_ProductReview]
    DELETE FROM [dbo].[Revindex_Storefront_ProductPart]
    DELETE FROM [dbo].[Revindex_Storefront_ProductComponent]
    DELETE FROM [dbo].[Revindex_Storefront_ProductVariant]
    DELETE FROM [dbo].[Revindex_Storefront_RightDefinition]
    DELETE FROM [dbo].[Revindex_Storefront_VoucherDefinition]
    DELETE FROM [dbo].[Revindex_Storefront_Product]
    DELETE FROM [dbo].[Revindex_Storefront_SalesPayment]
    DELETE FROM [dbo].[Revindex_Storefront_Category]
    DELETE FROM [dbo].[Revindex_Storefront_Distributor]
    DELETE FROM [dbo].[Revindex_Storefront_Manufacturer]
    DELETE FROM [dbo].[Revindex_Storefront_SalesOrder]
    DELETE FROM [dbo].[Revindex_Storefront_WishList]
    DELETE FROM [dbo].[Revindex_Storefront_TaxClass]
    DELETE FROM [dbo].[Revindex_Storefront_TaxProvider]
    DELETE FROM [dbo].[Revindex_Storefront_Seller]
  2. Open a new query and execute the following SQL statement to disable all constraints at your target database.

    EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
     
  3. Right mouse on your source database and click Tasks > Export Data
  4. Follow the wizard and select source database you are exporting the data from.
  5. Select the target database you are exporting the data to.
  6. Select "Copy data from one or more tables or views".
  7. Select all these tables:

    Revindex_Storefront_Category
    Revindex_Storefront_CrosssellProduct
    Revindex_Storefront_Distributor
    Revindex_Storefront_Gallery
    Revindex_Storefront_Manufacturer
    Revindex_Storefront_ProductXXX (all ProductXXX tables except Revindex_Storefront_ProductReview)
    Revindex_Storefront_RelatedProduct
    Revindex_Storefront_RequiredProduct
    Revindex_Storefront_RightDefinition
    Revindex_Storefront_Seller
    Revindex_Storefront_TaxClass
    Revindex_Storefront_TaxProvider
    Revindex_Storefront_VoucherDefinition
    Revindex_Storefront_Warehouse
     
  8. On each selected table, click on Edit Mapping and select the following:

    Enable identity insert 
    + Append rows to the destination table
    + Look for columns of type "timestamp" (e.g. RowVersion column) and set the Destination to <ignore>

    Note: You can use the CTRL or SHIFT keyboard to select multiple tables and make edits to all of the selected tables at once.
  9. Click Finish.
  10. Open a new query and execute the following SQL statement to re-enable all constraints at your target database.

    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'
     
  11. Execute the following statement to check the database integrity at your target database. If any data integrity failures are reported, you should rollback to your backup database and retry.

    DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
     
  12. Copy over all the files from your folder \DesktopModules\Revindex.Dnn.RevindexStorefront\Portals\X to the other server respectively where X is your portal number.
  13. Test your data

If you're running tests on a development/staging machine with production data copied over and you sell recurring products, make sure to disable any recurring orders or change the payment gateway credentials, otherwise it will automatically charge your customer's credit card when the order is due for renewal.

Comments


Powered by Revindex Wiki