BACKUP Backup Manual for XPert.NET

Table of Contents 1

Backup Manual for XPert.NET ............................................................................................... 3 1.1 1.2 1.3 1.4 1.5

2

Information for MS SQL Server 2005/2008 .......................................................................... 10 2.1 2.1.1 2.1.2 2.1.3 2.2 2.3 2.3.1 2.3.2 2.4

3

Requirements ................................................................................................................ 4 Deactivation of XPert.NET ............................................................................................ 5 Backing up the Components of XPert.NET.................................................................... 6 Recovering XPert.NET .................................................................................................. 7 Using a Repository ........................................................................................................ 7

Backup of a Database in the MS SQL Management Studio ........................................ 10 The First Backup ......................................................................................................... 10 Further Backups .......................................................................................................... 13 Hints on Disk Space .................................................................................................... 15 Restoring a Database in the MS SQL Management Studio ......................................... 15 Shrinking the Log File of a Database Manually............................................................ 18 Checking the Size of the Log File ................................................................................ 18 Cleansing the Log File in Order to Release Disk Space .............................................. 19 Creating a Backup Plan............................................................................................... 20

Statistics and Change Management.................................................................................... 29 3.1 3.2

Statistics...................................................................................................................... 29 Change Control ........................................................................................................... 29

BACKUP | INHALTSVERZEICHNIS | 2

1 Backup Manual for XPert.NET A backup is an essential element of your data security. The following manual thoroughly describes how to set up a backup for an XPert.NET installation in order to be able to recover it in the worst case. Moreover, requirements for a backup and a recovery are depicted here. A standard installation with the following parameters is assumed here. XPert.NET has been installed in the following paths and files on the server:

PLEASE NOTE: If different paths are used on your system, please enter them in the section below.

FOLDER

PHYSICAL STORAGE LOCATION

Main installation folder

C:\Program Files\Isonet\

Database folder

Depends on your MS SQL Server installation

Log folder

C:\Program Files\Isonet\Logfiles

Upload folder

C:\Program Files\Isonet\Uploads

Reporting Services database

Depends on your MS SQL Server installation

Paths to the folders in your installation: Main installation folder Database folder Log folder Upload folder Reporting Services database

The main installation folder contains all web pages necessary for running Xpert.NET. Additionally, all of the Windows services are installed there.



Note on distributed environments: In distributed environments, the web pages, web services and the Windows services can be installed on separate servers. Here, you have to execute the backup and the rollback on the respective servers.

BACKUP | BACKUP MANUAL FOR XPERT.NET | 3

In this document, we assume that the entire system has been installed on one server. In the database folder, the entire XPert.NET database is to be found. Thus, it contains the following databases (depending on your licensed modules): -

CMDB Knowledge Base LVer RM SecurityManager TM WorkflowManager XPert XPert_MASTER XPertArchive

In the log folder, the backup and log files created by the isonet ag before an update are filed. The upload folder contains the files that have been uploaded during the use of XPert.NET. These can be files, which have been previously attached to tickets, but also files that have been used to display the escalations in the ticket list, for example. The Reporting Server database contains all reports that can be displayed with XPert.NET. Before you can start the backup, you have to deactivate XPert.NET, so that no data can be written into the database during the backup process. Ideally, you would have an own backup plan for the SQL database and thus could be able to recover older versions as well.

1.1

Requirements

In order to perform a backup successfully, the following requirements have to be met. The backup is usually stored in the sub-folder Backup (in C:\Program Files\Isonet). On principle, database backup files can only be stored locally on the database server. It is not possible to select a drive in the network for this. The same applies for the recovery. You can enter the paths for the backups into the following table: Binary files XPert.NET databases (locally on the DB server!)

BACKUP | BACKUP MANUAL FOR XPERT.NET | 4

Requirements for the Backup of Binary Files The user logged onto the Windows system requires full access to the installation folder (default: C:\Program Files\Isonet). If another memory location is used for the backup, the full access is necessary there as well. Requirements for the Backup/Recovery of Databases Read and write permissions for the backup folder on the database server are necessary. Moreover, the SQL user used requires the rights for performing backups and recovering databases.

1.2

Deactivation of XPert.NET

The first step in deactivating XPert.NET is stopping all of the XPert.NET services. Those are all the services beginning with Isonet. Please mind to stop the Logging Manager last!

Figure 1-1

The sequence of the cancellation of thefurther services is not relevant. The second step is to deactivate the respective web services in the Internet Information Manager (IIS). You can achieve this by stopping the respective application pools. As an alternative to stopping the application pools individually, you can stop the WWW service as well, if there are no other webs installed on the server. By this, all of the web pages will be stopped. Now, XPert.NET should also be no longer accessible via the internet.

BACKUP | BACKUP MANUAL FOR XPERT.NET | 5

Figure 1-2

Thus, it can be ruled out that changes could be made to the existing data during the backup.

1.3

Backing up the Components of XPert.NET

Below, the three steps necessary for backing up XPert.NET are listed: 1.

2.

3.

Back up all of the databases on principle! This prevents discrepancies during recovery that may be necessary. You can find information on how to back up databases in Chapter Fehler! Verweisquelle konnte nicht gefunden werden. Fehler! Verweisquelle konnte nicht gefunden werden.. In order to back up the folder containing the binary installation and the web pages, navigate to the path C:\Program Files\Isonet in the Windows Explorer. Select all folders except for the folders Backup and Update. Subsequently, right click on the selected objects and select Send to -> Compressed (zipped) folder. Afterwards, assign a distinct name to the created ZIP file (preferably including a date) and move it to the Backup folder. The backup of the binary files is now complete. Back up the file Metabase.xml from the folder C:\Windows\system32\inetsrv\. It contains all of the information on the created web pages and the application pools. Copy the file into the Backup folder, specifying the date as well.

BACKUP | BACKUP MANUAL FOR XPERT.NET | 6

1.4

Recovering XPert.NET

In order to restore XPert.NET, you need a completely installed Windows Server system (for further information, see the documents SYSTEM REQUIREMENTS and INSTALLATION INSTRUCTIONS ). The following components should be installed or accessible: 1.

2.

3. 4.

Internet Information Service Message Queue Service MS SQL Server MS SQL Server Reporting Services Stop all of the Isonet services, if they have been installed. Stop the Internet Information Service (WWW service) as well and copy the Metabase.xml file into the directory C:\Windows\system32\inetsrv\. Thus, you have added all of the information about the previously created web pages and application pools again. You only have to perform this step, if these settings have been lost. Now, recover the databases. You can find information on how to recover databases in Chapter Fehler! Verweisquelle konnte nicht gefunden werden. Fehler! Verweisquelle konnte nicht gefunden werden.. Open the most current ZIP file in the Backup folder in the XPert.NET installation and copy its content into the installation path (default: C:\Program Files\Isonet). Start the script for the installation of the XPert.NET services by running Installservices.bat and then ServiceDescriptions.reg. Both files are can be found in the Isonet folder. This step is only necessary, if the services are not installed correctly any more.

Now you have installed the services, restored the database and created the web pages. Please start the application pools in the Internet Information Manager (IIS) and the XPert.NET services in the management console now, in this sequence: -

Isonet Logging Manager Isonet Security Manager Isonet Messaging Manger Isonet Workflow Manager Isonet Security Level Manger Isonet Mail2Ticket Isonet Agent

XPert.NET has now been recovered and can be used again.

1.5

Using a Repository

In order to update installed XPert.NET systems, repositories can be used. There are many different repository systems available, like for example SVN, CVS, Mercurial – only to name a few. The complete installation hierarchy is initially stored in this repository and all of the file operations are performed on a separate check out point.

BACKUP | BACKUP MANUAL FOR XPERT.NET | 7

Check-out Repository Check-in Workstation with separate Check-Out-Point for handling the Repository

Check-out Check-out

Test system

Web

Application

Live system

Web

Application

Figure 1-3 Thus, no separate backup is necessary anymore and the downtime for the actual system can be reduced to a minimum during an update, for example.



The databases will not be included when a repository is used, i.e. they have still to be backed up and recovered manually. Recovery via a Repository

Because no backup of the binary and configuration files is necessary, the recovery via a repository is described in the following. 1. Stop all of the Isonet services, if they have been installed. Stop the Internet Information Service (WWW service) as well and copy the Metabase.xml file into the directory C:\Windows\system32\inetsrv\. Thus, you have added all of the information about the previously created web pages and application pools again. You only have to perform this step, if these settings have been lost. 2. Now, recover the databases. You can find information on how to recover databases in Chapter Fehler! Verweisquelle konnte nicht gefunden werden. Fehler! Verweisquelle konnte nicht gefunden werden.. 3. Update the folder C:\Program Files\Isonet\ to the last working revision from the repository. Usually, this is always the next to last one. 4. Start the script for the installation of the XPert.NET services by running Installservices.bat and then ServiceDescriptions.reg. Both files are can be found in the Isonet folder. This step is only necessary, if the services are not installed correctly any more. Now you have installed the services, restored the database and created the web pages. Please start the application pools in the Internet Information Manager (IIS) and the XPert.NET services in the management console now, in this sequence: BACKUP | BACKUP MANUAL FOR XPERT.NET | 8

-

Isonet Logging Manager Isonet Security Manager Isonet Messaging Manger Isonet Workflow Manager Isonet Security Level Manger Isonet Mail2Ticket Isonet Agent

XPert.NET has now been recovered and can be used again.

BACKUP | BACKUP MANUAL FOR XPERT.NET | 9

2 Information for MS SQL Server 2005/2008 This chapter contains general information on saving, restoring and maintaining the databases. Due to relevance, only MS SQL Server 2005 and newer are discussed here. The SQL Server 2000 is not supported any more.

2.1 2.1.1

Backup of a Database in the MS SQL Management Studio The First Backup

Open the Management Studio of the MS SQL Server 2005 from the start menu. Expand the Databases node and highlight the database you want to back up. Right click on the database name and select Tasks  Back Up…

Figure 2-1

In the following dialogue, you can now make several settings.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 10

Figure 2-2

Select the backup type first. Please select the type Full when creating a first backup. Highlight Disk as the destination. If you do not want to use the preset path, remove it first and then add a new one via Add…. .

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 11

Figure 2-3 As an example, C:\Temp has been selected as a path and Backup_CMDB.bak as a file name.

After having selected a location and a name for the backup, click on Options on the left. For the first backup, leave all the default settings unchanged. In the Reliability section, you can activate the option for verifying the backup after it has been finished. You do not have to activate it, but it is recommended in order not to archive any defective backups.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 12

Figure 2-4

Click on Ok subsequently, and the backup will be performed. 2.1.2

Further Backups

If you want to perform further backups of this database, you have now the option to select Differential as a backup type.

Figure 2-5

Leave the destination unchanged on the same file. BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 13

Figure 2-6 Mind not to overwrite the old backup when saving the new one.

Mind not to overwrite the first backup, and select Append to the existing backup set instead, in order to add the new data to the first backup. Moreover, you should generally activate the verification of the backup for increased data security. If you now open the backup dialogue once again, you can view the contents of the backup set you created. For this purpose, open the backup dialogue and click on Contents. In the following dialogue, you can see the contents of the created backup set.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 14

Figure 2-7

2.1.3

Hints on Disk Space

Pay attention to the destination data medium having enough free disk space during the creation of backups. According to experience, database backups can become very big. If you automate the backups and run them every night, for example, it is more favorable not to use backup sets. Their disadvantage is that you cannot compress and then delete them afterwards, because of the SQL server trying to expand these sets every time. Thus, for automated backups, it is preferable to create backups always having the date and time of creation in their file name. You can compress these and transfer them to other mediums (e.g. DVD) and then delete them from the original drive. Thus, you have enough free disk space left for the next backups.

2.2

Restoring a Database in the MS SQL Management Studio

Open the Management Studio of the MS SQL Server. Right click on the node Database and select Restore Database. If you use the SQL Server 2008, select the following path in order to restore the database instead.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 15

Figure 2-8

Enter a name for the database and select From Device in the following dialogue. Subsequently, click on the button with the three dots in order to search for the file containing the backup.

Figure 2-9

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 16

Select File here and click on Add. Select the directory and the previously created backup file here. Subsequently, click on Ok in order to close the dialogue. Also click on Ok in the previous dialogue. Highlight all backup sets within this file and click on Ok subsequently. Now switch to the Options tab. If you have already created an empty database, into which you want to import the backup, activate the option Overwrite the existing database.

Figure 2-10

Moreover, you can change the paths under which the SQL server files the database files here. Thus, you can select a different partition on the server providing you with more disk space if necessary. The data will now be imported onto the SQL server and will be available afterwards again immediately.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 17

ATTENTION We generally advise you against storing the database files on the system drive. Thus, after a system crash, database files can be directly imported onto the server after a new installation without having to input a backup (which may no longer be up-to-date).

2.3

Shrinking the Log File of a Database Manually

In the life cycle of a database, it may happen that the log file becomes so huge that no more further operations can be added to it. For the user of an application, this manifests itself mostly in him not being able to complete any further action, because the data is not written into the database any more. This can cause considerable problems in the processes of a company. Thus, it is recommended to check the log files of databases on a regular basis. We will illustrate how to check the size and subsequently shrink the log file manually in the following sections. 2.3.1

Checking the Size of the Log File

Open the MS SQL Management Studio. Expand the Databases node and right click on the database, the size of which you want to check. Select Tasks  Shrink  Files from the context menu.

Figure 2-11 BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 18

In the dialogue now opening, you can check the size of the database and the disk space left. 2.3.2

Cleansing the Log File in Order to Release Disk Space

Select the designated database and click on the New Query button in the SQL Management Studio.

Figure 2-12

An empty query window opens on the desktop. Enter the following: BACKUP LOG WITH TRUNCATE_ONLY

with being the name of the database, whose log file you want to cleanse. Click on the Execute button afterwards or press the F5 key.

Figure 2-13

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 19

2.4

Creating a Backup Plan

In this section, we assume that you are logged onto the SQL server locally. Open the MS SQL Management Studio. Login in with an account that is sysadmin in the SQL group. Generally, all of the users belonging to the local admnistrator group are to be seen as such. Expand the node of your server and then the Management node. Now, right click on the Maintanance Plans folder and select New Maintenance Plan….

Figure 2-14

Assign a name to the plan and click on Ok subsequently.

Figure 2-15

On the right side, you can now see the header of the maintenance plan.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 20

Here, you can enter an additional description. Click on the Connections… button or, if you are using the SQL Server 2008, the Manage Connections… button subsequently.

Figure 2-16

In order to prepare the connection for the maintenance plan, highlight the connection specifed here and click on Edit.

Figure 2-17

If you are logged onto the SQL server you want to create the maintenance plan on locally, the presettings of the connection are all right so far. Simply enter a user name and the corresponding password the maintenance plan is to be executed with here. Subsequently, click on Ok in order to close the dialogue. Click on Ok in the Manager Connections dialogue as well.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 21

Figure 2-18

Now you still have to add execution times to the maintenace plan. For this, click on the button with the three dots behind the Schedule field. If you are using the SQL Server 2008, click on the date field instead.

Figure 2-19

A dialogue for defining the execution times opens. Enter a name for this time definition into the uppermost field and select the entry Recurring under Schedule type.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 22

Figure 2-20

In this example, we choose a daily execution at 01:00 am. Thus, set the execution to Daily in the Frequency section. Leave the default setting for the entry Recurs every unchanged. This means that the plan will be executed every day. Now, enter 01:00:00 in the Daily frequency section. In the Duration section, set the start date of this plan to the current or a future date and check the option No end date. After the specified settings have been made, your dialogue should look approximately the same as the one in the figure above. In the lowermost section, you can see a short summary of your configurations. Confirm this dialogue with Ok. Now, we still have to create the actual plan – i.e. what is to be made. For this purpose, you can see a bar with miscellaneous actions that can be strung together in a workflow on the lower left side.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 23

Figure 2-21

Move the action Back Up Database Task onto the work area on the right side via drag & drop. With this, you have inserted the first action. Now, double click (left key) on the task symbol in the work area.

Figure 2-22

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 24

Select the databases you want to save under Databases.

Figure 2-23

Only the four databases visible here have been selected in this example. Click on Ok subsequently. Select either Full or Differential as a Backup type. Activate the option Create a backup file for every database and select Create a sub-directory for each database for a more clarity as well. Thus, you have a good overview over your saved databases. Finally, activate the option Verify backup integrity in order to recheck the backup after its creation. Close the dialogue with Ok.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 25

Figure 2-24

The red warning cross in the action should have disappeared by now. If you move the cursor over the action, you can receive information on possible misconfigurations in a tool tip. After a backup of the database via the recently created task, the database can now be cleansed. For this, drag the action Shrink Database Task from the tool box into the work area with your cursor. Highlight the action Back Up Database Task in the work area. Now, you can see a green arrow. Left click on this arrow and drag it over the action Shrink Database Task. Release the mouse key and you should see the following picture. The two actions now are interlinked and will be executed one after another.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 26

Figure 2-25

Double click on the action Shrink Database Task (or select Edit from the context menu). Select your database connection and the respective databases in this dialogue. You can leave the further settings at the default values. Confirm the settings with a click on Ok.

Figure 2-26

Click on one of the two diskette symbols in the tool bar above in order to save this backup plan.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 27

ATTENTION Do not forget to start the SQL Server Agent! Otherwise the backup plan will not be executed.

Figure 2-27 For testing and demonstration purposes, the backup time has been changed to the current time.

As you can see, a several numbers are attached to the backup’s name. They are composed of the following digits: The first four digits denote the year (2010). The digits 5 and 6 depict the month and the digits 7 and 8 the day of the backup. The next six digits denote the time (14:29:05 in this example). The last seven digits are not to be minded for the time being.

BACKUP | INFORMATION FOR MS SQL SERVER 2005/2008 | 28

3

Statistics and Change Management

3.1

Statistics

By

Jan Hildebrandt

Date

03.09.2010

Doc-ID

DOC-200810-010

Version

3.1

Status

Released

Replaces version

3.0

Release date

1.2.2011

Valid from

Immediately

Valid until

Cancellation

Document name

Backup XPert.NET 3.1_EN

3.2

Change Control

VERSION

DATE

EXECUTED BY

MODIFICATI ON

1.0

13.6.2007

Jan Hildebrandt

Created

1.1

2.8.2007

Marco Mehl

Revised

Enhanced with backup & restore DB

1.2

14.5.2008

Marco Mehl

Revised

Enhanced with shrink logfile

1.3

21.5.2008

Marco Mehl

Revised

Enhanced with shrink database

3.0

20.8.2010

Maik Wisatzke

Revised

New design, linguistic revision, adjustments for version 3.4

3.1

20.1.2011

Marco Mehl

Revised

Backup and resore revised

COMMENT

BACKUP | STATISTICS AND CHANGE MANAGEMENT | 29

Copyright © 2011 isonet ag, Engineering und Beratung, Zürich

This document is intellectual property of isonet AG and protected by copyright. It must not be saved, copied, duplicated, or passed on photo mechanically, digitally, or through any other means. Nor must it be used for the execution of projects. The companies directly addressed in this project have the right to use this document for their purposes exclusively within the scope of this offer