Layton ServiceDesk - Procedures - General - Sending your Database to Support
m (Updated details) |
|||
(15 intermediate revisions by one user not shown) | |||
Line 1: | Line 1: | ||
− | <p align="right">[[File:btn_back_up.png|link=User Guide for Layton | + | <p align="right">[[File:btn_back_up.png|link=User Guide for Layton ServiceDesk]] [[User Guide for Layton ServiceDesk|<u>Back to Contents</u>]]</p> |
− | On occasion, Support may need to examine your database to understand the cause of an issue or to repair your database. | + | __FORCETOC__ |
+ | =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. | ||
− | + | =Preparatory Steps= | |
+ | ==Gather Information About the Database== | ||
+ | Collect the following information for later use: | ||
− | #Use SQL Server Management Studio (SSMS) to back up the SQL database to a .bak file. Note the following settings:<br /><p>[[File:Lsd db backup01.png|link=]]<br /><br />[[File:Lsd db backup02.png|link=]]</p> | + | #The '''[[Layton ServiceDesk - Procedures - General - How to Find the Version Number|application version]]''' of your installation of Layton ServiceDesk. |
− | + | #The version of SQL Server you are using. | |
− | + | #The password for the built-in ''admin'' account. If you have security concerns about sending the database with the admin password, '''[[Layton ServiceDesk - Settings - Company Structure - Manage Analyst|change it]]''' prior to backup and sending. | |
+ | |||
+ | ==Create Database Backup== | ||
+ | #'''[https://msdn.microsoft.com/en-us/library/ms187510.aspx Use SQL Server Management Studio (SSMS) to back up the SQL database to a .bak file]'''. Note the following settings:<br /><p>[[File:Lsd db backup01.png|link=]]<br /><br />[[File:Lsd db backup02.png|link=]]</p> | ||
+ | |||
+ | =Preparing the Database for Sending= | ||
+ | ==Complete Database== | ||
+ | In most instances, you will be sending the complete database. In this case, you can skip to step '''[[Layton ServiceDesk - Procedures - General - Sending your Database to Support#Prepare File for Upload|Prepare File for Upload]]'''. | ||
+ | |||
+ | ==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.<br /> | ||
+ | 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 '''[[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 /> | ||
+ | 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 | ||
+ | |||
+ | ==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= | ||
+ | ==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 | ||
+ | |||
+ | ==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 '''[http://www.7-zip.org/ 7-Zip]''' and send the password via direct email to '''[mailto:[email protected] Support]''' | ||
+ | |||
+ | ==Zip the Backup File== | ||
+ | '''Important!''' You must zip the file before sending to greatly reduce its size. | ||
+ | |||
+ | =Send the Database= | ||
+ | Send the .zip file to Support via our secure '''[http://laytontech.leapfile.net upload service]'''. | ||
+ | <p align="right">[[File:btn_back_up.png|link=User Guide for Layton ServiceDesk]] [[User Guide for Layton ServiceDesk|<u>Back to Contents</u>]]</p> |
Latest revision as of 00:02, 10 February 2022
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:
- The application version of your installation of Layton ServiceDesk.
- The version of SQL Server you are using.
- 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
- Use SQL Server Management Studio (SSMS) to back up the SQL database to a .bak file. Note the following settings:
[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.