How do I migrate the Reporting database to another MS SQL Server instance lower version?

Email It to Me Print this Page
Question How do I migrate the Reporting databases to another MS SQL Server instance of a downgraded version?
Answer There are a few options to downgrade the database from a higher version of SQL Server to a lower version of SQL Server. These options include:

- Configure product to the destination SQL Server , create product database and upload all historical data to it from the local repository folder with the Netwrix Auditor DB Importer tool (can be found in the product installation folder, by default: C:\Program Files (x86)\Netwrix\Active Directory Auditing ); 

- Migrate database with Generate Scripts wizard of SQL Server Management Studio. For it please perform the following steps:
1. Script the schema of the database on the source SQL Server instance using the Generate Scripts wizard of the SQL Server Management Studio interface.

1.1 In Object Explorer connect to the SQL server, right-click database, expand Tasks and choose“Generate Scripts…”.

User-added image

1.2 This launches Generate and Publish Scripts wizard. Click Next, to skip the Introduction screen and proceed to the Choose Objects page.

User-added image

1.3 On the Choose Objects page, choose “Script entire database and all database objects”, and then click Next to proceed to the “Set Scripting Options” page.

User-added image

1.4 On the Set Scripting Options page, specify the location where you want to save the script file, and then click the Advanced button.

User-added image

1.5 In the Advanced Scripting Options dialog box, set Script Triggers, Indexes and Primary Key options to True, Script for Server Version to <version of the destination SQL server instance>, and Types of data to script to Schema and Data.  This last option is key because this is what generates the data per table.

User-added image

1.6 Once done, click OK, to close the Advanced Scripting Options dialog box and return to the Set Scripting Options page. In the Set Scripting Options page, click Next to continue to the Summary page.

1.7 After reviewing your selections on the Summary page, click Next to generate scripts.

User-added image

1.8 Once scripts are generated successfully, choose the Finish button to close the Generate and Publish Scripts wizard.

User-added image

2. Connect to the destination SQL Server instance, and then run the SQL scripts that were generated, to create the database schema and copy its data.

2.1 In Object Explorer connect to the destination SQL Server instance and then in SQL Server Management Studio open the SQL Server script you saved in Step-1.

User-added image
 
User-added image

User-added image

2.2. Modify the script, to specify the correct location for the database data and log files. Once done, run the script to create the database on the destination SQL Server instance.

User-added image

2.3 Upon successful execution, refresh the Database folder in Object Explorer.

User-added image
Was this information helpful?