ACTT MS - SQL - Extract Script Instructions

Automated Controls Testing Tool
Application Content Extract Guide

These are the instructions for executing the Deloitte ACTT script. This script will read database
configuration tables and produce several .ACTT/.TXT/.LOG files. These files are plain text files.
Script Security
The script contains read only queries and does not create or update any tables within the database. You
can and should review the script to obtain comfort over what it is performing. If there are any concerns,
it is appropriate to execute this script using database credentials that do not have update database access.
Script Impact on the Database

Depending on the size of the database, it is possible for the script to run for a significant amount of
time. We recommend that the script be executed on a non-production environment to gauge its impact
before it is executed on a production database. Always run the script with the prescribed access
mentioned in our instructions for successful and complete extraction of relevant data.
What you need to run the script

You should have received a zip file that contains the following files:
1) ‘ACTT MS_SQL_Extract Script Instructions.doc’ - Instructions document

2) ‘ACTT_MS_SQL_Script.ps1’ – MS SQL Script developed in PowerShell
In addition to the above files, you need below system requirements for running the script:
1) PowerShell version on your server needs to be version 3.0 and above.
2) PowerShell Execution policy needs to be set to RemoteSigned. The command below needs to be
executed on PowerShell prompt prior to executing the ACTT script on the server and this setting affects
only the current Windows PowerShell session.
Set-ExecutionPolicy RemoteSigned -Scope Process
Note
When running the SQL Query, standard change management procedures should be applied.
Please contact your Deloitte team member for assistance should you encounter any errors.
Step-by-Step Instructions:
Note: The User ID that will be used to extract data should have read access on SYSTEM tables as
well.
Step I: Extract the files from the zip file to a folder on the Windows server(s) where MS SQL
Database is installed.
DB Content Extraction Guide for MS SQL Server DB

Last updated June 03, 2020
Step II: Set the PowerShell Execution Policy to RemoteSigned.

Open PowerShell window and run the below command.
Set-ExecutionPolicy RemoteSigned -Scope Process
Step III: Execute the PowerShell Script (by right click the file and choosing “Run with
PowerShell”)
Enter the SQL Server Instance names in semicolon(;) separated format: Provide the list of SQL
servers from which data needs to be extracted in single go.
Enter the Mode of Authentication:

Please Select ‘1’ only if Windows Authentication is enabled for the logged in user. Else select ‘2’ to key
in your MS SQL DB username and password. This step repeats and extraction happens server by server.

Enter the DataBase Name:
1. For single Database: Enter database name(case sensitive based on your instance configuration)
2. For Multiple Databases: Enter database names with comma(,) separation
Data will be extracted on the location from where the Script was executed.
This could take a few minutes, but could take longer. Some of the factors that will impact the time
include:
 Amount of data being extracted
 Connection speed to the database
 Processing power of the database
 Other processes running concurrently on the database
Step IV: Send the files to your Deloitte contact

The script will attempt to compress the files into a zip file. If this happened successfully, you will find
the relevant multiple MS SQL server zip files in the same directory where you executed the Powershell
script.
If Multiple SQL servers (SERVER_1, SERVER_2, SERVER_3) were selected for extraction, after the
successful execution you will have three zip files <SQLInstanceName>.zip created at the location in
which the script was executed.

Common Errors:
1. Extracting data with the wrong SQL server instance name.
Error: Get-Content: Cannot find path ‘…’ because it does not exist.
Remove-Item : Cannot find path ‘….' because it does not exist.
Example: Error highlighted below.
Solution: When prompts for SQL server name, provide the correct SQL Server full name as
displayed in SQL Server Management Studios and please make sure username and password are
correct.
From SQL server management studio


ACTT MS - SQL - Extract Script Instructions

Uploaded by

Copyright:

Available Formats

ACTT MS - SQL - Extract Script Instructions

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ACTT MS - SQL - Extract Script Instructions

Uploaded by

Copyright:

Available Formats

What is the purpose of the script?

What is the purpose of the script?

What are the system requirements for running the script?

What are the system requirements for running the script?

Automated Controls Testing Tool

Application Content Extract Guide

Script Impact on the Database

What you need to run the script

1) ‘ACTT MS_SQL_Extract Script Instructions.doc’ - Instructions document

Set-ExecutionPolicy RemoteSigned -Scope Process

DB Content Extraction Guide for MS SQL Server DB

Step II: Set the PowerShell Execution Policy to RemoteSigned.

Set-ExecutionPolicy RemoteSigned -Scope Process

Enter the Mode of Authentication:

DB Content Extraction Guide for MS SQL Server DB

Enter the DataBase Name:

2. For Multiple Databases: Enter database names with comma(,) separation

Step IV: Send the files to your Deloitte contact

DB Content Extraction Guide for MS SQL Server DB

1. Extracting data with the wrong SQL server instance name.

Remove-Item : Cannot find path ‘….' because it does not exist.

Example: Error highlighted below.

From SQL server management studio

DB Content Extraction Guide for MS SQL Server DB

You might also like