Layton ServiceDesk - Procedures - General - Sending your Database to Support

From Layton Support
(Difference between revisions)
Jump to: navigation, search
m (Changed section title)
m (Updated details)
 
(5 intermediate revisions by one user not shown)
Line 22: Line 22:
 
If there is a security concern regarding the contents of the database, you can strip out all the Requests prior to sending.
 
If there is a security concern regarding the contents of the database, you can strip out all the Requests prior to sending.
  
1.  In Management Studio, restore the database backup you just made, with a different name, in order to produce a copy.
+
1.  In Management Studio, restore the database backup you just made, with a different name, in order to produce a copy.<br />
 
+
 
2. In Management Studio, on the database copy, run this query to set the status of every Request to ''Closed'':
 
2. In Management Studio, on the database copy, run this query to set the status of every Request to ''Closed'':
  
Line 29: Line 28:
 
     SET    sys_requeststatus = 0
 
     SET    sys_requeststatus = 0
  
3. Preferably, in your test Layton ServiceDesk installation, or if you don't have one, your live installation (this will be disruptive to anyone using the system, so do it when users are offline), launch DBTool to '''[[Layton ServiceDesk - Installation - Connect to Existing Database|Connect to Existing Database]]'''. This will connect Layton ServiceDesk to the database copy.
+
3. Preferably, in your test Layton ServiceDesk installation, or if you don't have one, your live installation (this will be disruptive to anyone using the system, so do it when users are offline), launch DBTool to '''[[Layton ServiceDesk - Installation - Connect to Existing Database|Connect to Existing Database]]'''. This will connect Layton ServiceDesk to the database copy.<br />
 
+
4. Launch Layton ServiceDesk and purge all closed Requests per '''[[Layton ServiceDesk - Utilities - Purging#Requests|this procedure]]'''.<br />
4. Launch Layton ServiceDesk and purge all closed Requests per '''[[Layton ServiceDesk - Utilities - Purging#Requests|this procedure]]'''.
+
 
+
 
5. In Layton ServiceDesk, check that there are no Requests. If you want to do this in Management Studio, use this query. The result should be ''0'':
 
5. In Layton ServiceDesk, check that there are no Requests. If you want to do this in Management Studio, use this query. The result should be ''0'':
  
 
     SELECT  COUNT(sys_request_id)
 
     SELECT  COUNT(sys_request_id)
 
     FROM    request
 
     FROM    request
 +
 +
==Optional: Database Without End-User and Analyst Details==
 +
Support may indicate that it may be necessary to test with End-User and/or Analyst user names, but you don't want to send personal details like phone numbers, email address and physical address. This SQL Server script will strip these out prior to backing up. Do this on a copy of the database.
 +
 +
    UPDATE  euser
 +
    SET    sys_phone = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_fax = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_email = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_euserpwd = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_cont_startdate = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_cont_enddate = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_mobile = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_addstreet1 = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_addstreet2 = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_addsuburb = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_addstate = NULL
 +
   
 +
    UPDATE  euser
 +
    SET    sys_addpostcode = NULL
 +
 +
    UPDATE  [user]
 +
    SET    sys_email = NULL
 +
   
 +
    UPDATE  [user]
 +
    SET    sys_escalate1email = NULL
 +
   
 +
    UPDATE  [user]
 +
    SET    sys_escalate2email = NULL
 +
   
 +
    UPDATE  [user]
 +
    SET    sys_escalate3email = NULL
 +
   
 +
    UPDATE  [user]
 +
    SET    sys_resolveemail = NULL
 +
   
 +
    UPDATE  [user]
 +
    SET    sys_respondemail = NULL
 +
   
 +
    UPDATE  [user]
 +
    SET    sys_userpwd = NULL
  
 
=Prepare File for Upload=
 
=Prepare File for Upload=
Line 44: Line 101:
 
     lsd_VERSION_sqlVERSION_YEARMONTHDAY.bak
 
     lsd_VERSION_sqlVERSION_YEARMONTHDAY.bak
  
For example, if you were backing up a version 6.5.9 database running on SQL Server 2008 R2 on 25 Jan. 2016, the resultant filename would be:
+
For example, if you were backing up a version 6.8.7 database running on SQL Server 2019 on 10 Feb. 2022, the resultant filename would be:
  
     lsd_659_sql2008r2_20160125.bak
+
     lsd_687_sql2019_20220210.bak
  
 
==Optional: Encrypt the Backup File==
 
==Optional: Encrypt the Backup File==

Latest revision as of 00:02, 10 February 2022

Btn back up.png Back to Contents

Contents

[edit] Overview

On occasion, Support may need to examine your database to understand the cause of an issue or to repair your database. There is a preparatory procedure, and two different procedures depending on whether you will be sending the whole database or the database without Requests.

[edit] Preparatory Steps

[edit] Gather Information About the Database

Collect the following information for later use:

  1. The application version of your installation of Layton ServiceDesk.
  2. The version of SQL Server you are using.
  3. The password for the built-in admin account. If you have security concerns about sending the database with the admin password, change it prior to backup and sending.

[edit] Create Database Backup

  1. Use SQL Server Management Studio (SSMS) to back up the SQL database to a .bak file. Note the following settings:

    Lsd db backup01.png

    Lsd db backup02.png

[edit] Preparing the Database for Sending

[edit] Complete Database

In most instances, you will be sending the complete database. In this case, you can skip to step Prepare File for Upload.

[edit] Optional: Database Without Requests

If there is a security concern regarding the contents of the database, you can strip out all the Requests prior to sending.

1. In Management Studio, restore the database backup you just made, with a different name, in order to produce a copy.
2. In Management Studio, on the database copy, run this query to set the status of every Request to Closed:

   UPDATE  request
   SET     sys_requeststatus = 0

3. Preferably, in your test Layton ServiceDesk installation, or if you don't have one, your live installation (this will be disruptive to anyone using the system, so do it when users are offline), launch DBTool to Connect to Existing Database. This will connect Layton ServiceDesk to the database copy.
4. Launch Layton ServiceDesk and purge all closed Requests per this procedure.
5. In Layton ServiceDesk, check that there are no Requests. If you want to do this in Management Studio, use this query. The result should be 0:

   SELECT  COUNT(sys_request_id)
   FROM    request

[edit] Optional: Database Without End-User and Analyst Details

Support may indicate that it may be necessary to test with End-User and/or Analyst user names, but you don't want to send personal details like phone numbers, email address and physical address. This SQL Server script will strip these out prior to backing up. Do this on a copy of the database.

   UPDATE  euser
   SET     sys_phone = NULL
   
   UPDATE  euser
   SET     sys_fax = NULL
   
   UPDATE  euser
   SET     sys_email = NULL
   
   UPDATE  euser
   SET     sys_euserpwd = NULL
   
   UPDATE  euser
   SET     sys_cont_startdate = NULL
   
   UPDATE  euser
   SET     sys_cont_enddate = NULL
   
   UPDATE  euser
   SET     sys_mobile = NULL
   
   UPDATE  euser
   SET     sys_addstreet1 = NULL
   
   UPDATE  euser
   SET     sys_addstreet2 = NULL
   
   UPDATE  euser
   SET     sys_addsuburb = NULL
   
   UPDATE  euser
   SET     sys_addstate = NULL
   
   UPDATE  euser
   SET     sys_addpostcode = NULL
   UPDATE  [user]
   SET     sys_email = NULL
   
   UPDATE  [user]
   SET     sys_escalate1email = NULL
   
   UPDATE  [user]
   SET     sys_escalate2email = NULL
   
   UPDATE  [user]
   SET     sys_escalate3email = NULL
   
   UPDATE  [user]
   SET     sys_resolveemail = NULL
   
   UPDATE  [user]
   SET     sys_respondemail = NULL
   
   UPDATE  [user]
   SET     sys_userpwd = NULL

[edit] Prepare File for Upload

[edit] Rename the Backup File

Please use this naming convention for the backup file:

   lsd_VERSION_sqlVERSION_YEARMONTHDAY.bak

For example, if you were backing up a version 6.8.7 database running on SQL Server 2019 on 10 Feb. 2022, the resultant filename would be:

   lsd_687_sql2019_20220210.bak

[edit] Optional: Encrypt the Backup File

If you are concerned about the security of the backup file, encrypt it in a zip file using a tool like 7-Zip and send the password via direct email to Support

[edit] Zip the Backup File

Important! You must zip the file before sending to greatly reduce its size.

[edit] Send the Database

Send the .zip file to Support via our secure upload service.

Btn back up.png Back to Contents

Personal tools
Namespaces

Variants
Actions
Main Page
Online User Guides
General Support
Release Notes
Toolbox