Purging Data from the Database

Use the procedure below to purge Server data from your SQL database. If the database is active, you should delete the records in small "chunks," perhaps 1000 at a time, so that you do not affect the responsiveness of the SQL server for new/ongoing transactions. If you run script after hours or on a test system, you can delete all of the records in larger chunks.

Anytime you edit a file, you should make a backup copy first.

To purge the data

  1. Open the script PurgeEftData.txt in a text editor and edit the following values:

    1. If the database name is not EFTDB, modify the following line with the name of the database: USE EFTDB.

    2. All records BEFORE the "DeleteTo" date are affected. If you want all rows to be affected, edit the date and time to some time in the future:

      SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'
       

    3. "DeleteScope" is the number of rows to be deleted and how quickly they are deleted. Leave the number of rows as '1000' or change it to another static value or a percentage of rows to be deleted.

      SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'

      Examples are commented in the top of the script and shown below:

      --SELECT @DeleteTo = '1/1/2006', @DeleteScope = '50%'
      --SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'
      --SELECT @DeleteTo = '1/1/2007', @DeleteScope = '2000000000'

  2. Save the script with a .sql extension. For example, save it as PurgeSQLEFTData.sql.

  3. After you have edited and saved the script, you can craft the proper command line for "oSQL" to connect to the database. The example below assumes you have kept the same name for the file.

    1. Open a command prompt. (Click Start > Run. Type cmd, then press ENTER.)

    2. Type the following to execute the sql file:

      [path to oSQL]\oSQL.exe -S [server address] -U [username] -P [password] -i "[path\to\PurgeSQLEftData.sql]\PurgeSQLEftData.sql"

      For example, type:

      "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\oSQL.exe" -S 192.168.19.17 -U iuser -P asd123!f$s1 -i "C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Server Enterprise\SQL Server\PurgeSQLEftData.sql"

For additional information on the oSQL utility, including common script samples, refer to osql Utility on microsoft.com.