• No results found

Database Back-Up and Restore

N/A
N/A
Protected

Academic year: 2021

Share "Database Back-Up and Restore"

Copied!
16
0
0

Loading.... (view fulltext now)

Full text

(1)

Database Back-Up and Restore

Introduction

To ensure the data held in People inc. is secure regular database backups of the databases must be taken. It is also essential that the procedure for restoring these backups is understood and that this procedure is tested from time to time to ensure that it works.

It is easy to assume that because the People Inc. databases are stored on a server that they are automatically backed-up every night. In practice, it is not uncommon for users to find that this is not the case and that when needed, restoring a back-up of the databases is more difficult than they expected it to be. Users should therefore:

 Make sure they understand exactly what the current arrangements for backups are.

 Make sure they know how often database back-ups are taken and for how long they are kept

 Make sure they know what step are taken to ensure the security of the back-up media

 Make sure that they know the procedure and time required to restore database backups

 If possible arrange for a ‘practice’ restore to make sure the procedure works

Users may have to ask for the database backup arrangements to be changed if they feel their data is not backed-up regularly enough, or that the back-ups are not kept for long enough.

This document is intended only as a guide. While at P&A Software we endeavour to provide advice and help where the security of the data held in our systems is concerned, it is the responsibility of the client’s IT support team to ensure the security and integrity of the computer environment used by system users. P&A Software to not support the SQL Server database software; the client’s IT staff should ensure that they have the skills, knowledge and resources to do this. In particular, from time to time Microsoft may update or release new versions of SQL Server. P&A Software do not undertake to keep this document complete or up-to-date with respect to these changes. The client’s IT support team are responsible for locating and understanding any version-specific information associated with the configuration of back-ups on their server.

(2)

Creating Database Backups

There are a number of options available to users when setting up backups. This document details these options. These are:

 Creating back-ups using the System Administrator module (ad-hoc/one off back-ups)

 Creating back-ups from SQL Server 2005 (regular/scheduled back-ups)

 Running a back-up script with SQL Express/MSDE 2000 (regular/scheduled back-up)

The People Inc. system manages information within a number of SQL Server databases. The standard system uses 4 databases, but it is possible to use just 3, and some installations may have more (depending on the number of user databases defined). All of the databases have to be backed-up at the same time to generate a valid back-up copy of the system (and these files have to be kept together as a set). A full People Inc. database back-up contains all user data, together with screen, table, letter and report definitions; in fact everything needed to recover the system. If it is necessary to restore a backup an entire set of database backups from a particular date must all be restored.

It is not possible to create a backup of your system by copying the live database files themselves. This is because these files are permanently in use (unless the server is first stopped). The following approaches can be taken.

Creating database back-ups using People Inc.

This approach is recommended for one-off or ad-hoc back-ups.

(3)

Note: the backup option is not available in the Management Console in the main People Inc. system; back-ups can only be created from the System Administrator module.

Select shared data and all other databases.

When back-ups are created in this way, the backup files are created in the folder assigned for backups within People Inc.; by default the files are created in the folder:

C:\Program Files\PA Group\People Inc\Backup

The path to the back-up folder can be checked using the Options > Show Backup Path menu item in the system administrator. This location can be modified using the Maintenance Manager utility. Note that backups created in this way are saved in this folder on the database server This is not normally the same computer that the user uses to access the system and users will not normally be able to see this folder from their PC.

(4)

The files produced by a backup are a back-up set and should be kept together, and restored as a set. Note that a backup set may have more than 4 files. To keep the back-up safe, copy the files produces to a suitable back-up device (tape or CD).

It is recommended that the back-up is labelled with the system/database version (for example ‘3.4.2.101’). The version number can be found by selecting help>about on the People Inc. menu. It is only possible to restore a back-up of a particular database version onto a system with the same version number.

Note that, when a back-up is restored, all existing data and screen/letter/report definitions are replaced with the copies held in the backup.

Automating back-ups using SQL Server 2005

This approach is recommended for regularly, daily or weekly back-ups.

For users managing their databases using a full copy of SQL Server, backups can be automated using the Maintenance Plans facility within SQL Server management studio.

(5)

The following screen is displayed

The wizard enables you to choose which databases will be backed up and also to set a schedule. Enter the server name and select Windows Authentication

(6)

The following screen is displayed

Select “Back up database (full)” and press next.

(7)

The following screen is displayed

(8)

The same screen now allows you to select the path and extension for the backup files produced.

(9)

Press change (in the screen above)

(10)

The next screen (not shown) is for configuring an optional report on the backup procedure, complete this screen and press finish.

The final screen confirms that you Maintenance Plan was configured successfully.

Note that the files created when running a back-up using SQL Server can contain multiple back-ups (of the same database). If the user wishes to use the data-restore functionality provided with People Inc. (recommended), they should ensure that back-up files only contain a single database backup. To do this, re-select the option re-format the back-up file each time a back-up is created.

(11)

Back-ups of databases running under SQLEXPRESS (2005)

This backup method is only appropriate for MS SQL Server 2005 Express. The user must download and install SQL Server 2005 Management Studio Express before implementing this approach to database back-up. This is a free tool from Microsoft and will be used to create a script to backup the People Inc. databases. Launch Microsoft SQL Server Management Studio Express and connect to the SQL Server instance that hosts the People Inc. databases (this is normally the \SQLEXPRESS instance). Expand the Databases node; this will display a list of databases including the People Inc. databases.

Right click the first database and select Tasks > Backup. A dialog box appears enabling the user to configure backup options (type of backup, backup destination etc.).

Select the desired options and then click the Options page (from the list on the left-hand side). Select further options (for example, Overwrite all existing backup sets).

Once all options are set, select Script > Action to File and enter a desired file name, for example, DatabaseBackup, and specify a location to save the file.

(12)

The contents of the DatabaseBackup.SQL file will look like this:

BACKUP DATABASE [redpi002_1] TO DISK = N'C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\Backup\redpi002_1.bak' WITH NOFORMAT, NOINIT, NAME = N'redpi002_1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

To test the DatabaseBackup.SQL file run the following from a command prompt.

sqlcmd -S .\SQLEXPRESS -i "C:\<path to DatabaseBackup.sql file>\DatabaseBackup.sql"

If the redpi002_1.bak file was created with the correct data and time stamp then you know your script works. By default the redpi002_1.bak is located in:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ You can automate the backup process by creating two Scheduled Tasks. SQLCMD Scheduled Task

First, create a Scheduled Task to automate the .SQL script created above. Use the Scheduled Task Wizard and when asked to select a program browse to use browse to C:\Program Files\Microsoft SQL

Server\90\Tools\binn\ SQLCMD.exe.

Define the Schedule Task parameters accordingly and click Finish.

Go the properties of the newly created Scheduled Task and edit the Run command as such.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -i “C:\Program Files\Microsoft SQL Server\DatabaseBackup.sql”

Copy redpi002_1.bak Scheduled Task

Next, create a simple batch file to copy the redpi002_1.bak from the local server to a network share located on server being backed up regularly. For example, create a file named, “Copy redpi002_1bak.cmd”, with the following contents.

copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ redpi002_2.bak" "\\<servername>\<sharename>\"

Lastly, create a simple Command Prompt Scheduled Task and configure accordingly. (Make sure to run this Scheduled Task after the SQLCMD Scheduled Task.) Go to the properties of the newly created Scheduled Task and edit the Run command to point to the location of Copy redpi002_1bak.cmd (or your respective batch file name).

(13)

Back-ups of databases running under MSDE 2000

This is an alternative approach designed for users who manage their databases using MSDE 2000. To automate backups, it is recommended that the following script is run within MSDE using a utility called OSQL.

This script can be called from a batch file by using the command:

osql -Usa -Ppassword -ibackup_script.sql -obackup_log.log

where ‘password’ is the password of the SA account on the server

exec sp_dropdevice 'redshared30_backup', 'DELFILE' go

exec sp_addumpdevice 'disk', 'redshared30_backup',

'd:\backups\redshared30_backup.BAK' EXEC ('BACKUP DATABASE [redshared30] TO [redshared30_backup] WITH FORMAT, NOUNLOAD , NAME =''redshared30 backup'', STATS = 100, DESCRIPTION = ''redshared30backup''')

go

exec sp_dropdevice 'redpi002_1_backup', 'DELFILE' go

exec sp_addumpdevice 'disk', ' redpi002_1_backup', 'd:\backups\ redpi002_1_backup.BAK' EXEC ('BACKUP DATABASE [redpi002_1] TO

[redpi002_1_backup] WITH FORMAT, NOUNLOAD , NAME ='' redpi002_1 backup'', STATS = 100, DESCRIPTION = '' redpi002_1backup''')

go

exec sp_dropdevice ' redpi002_2_backup', 'DELFILE' go

exec sp_addumpdevice 'disk', ' redpi002_2_backup', 'd:\backups\ redpi002_2_backup.BAK' EXEC ('BACKUP DATABASE [redpi002_2] TO

[redpi002_2_backup] WITH FORMAT, NOUNLOAD , NAME ='' redpi002_2 backup'', STATS = 100, DESCRIPTION = '' redpi002_2backup''')

go

exec sp_dropdevice ' redpi002_master_backup', 'DELFILE' go

exec sp_addumpdevice 'disk', ' redpi002_master_backup', 'd:\backups\ redpi002_master_backup.BAK' EXEC ('BACKUP DATABASE [redpi002_master] TO [redpi002_master_backup] WITH FORMAT, NOUNLOAD , NAME ='' redpi002_master backup'', STATS = 100, DESCRIPTION = '' redpi002_masterbackup''')

(14)

Restoring Database Backups

Restoring database back-ups using Maintenance Manager

Restoring People Inc. databases enables users to undo changes made in the system, for example if the user wishes to recover data that has been inadvertently overwritten or modified.

We recommend that users restore databases using the Maintenance Manager utility. When backups are restored in this way, data, screen definitions, letter and report templates, user accounts and preferences are all overwritten. In addition the Maintenance Manager utility creates any missing databases and configures the correct login details within SQL Server. A copy of this utility can be found in the tools folder on the installation CD. Note that the version of Maintenance Manager used should match the version of People Inc.

Any set of People Inc database back-up files can be restored onto SQL Server using the Maintenance Manager utility. A backup set will consist of a back-up of the redshared30 database, a backup of the master database, and individual back-ups of any user databases. A new installation of People Inc. has 2 user databases, but user may create more databases as they use the system. To avoid potential problems, People Inc. backups should always be restored as a complete set; individual database backups within a set should never be substituted for a database backup from another backup set. Before restoring databases, check the dates of the back-up files to make sure they were created at the same time.

If the backups were created using SQL Server, it may be necessary to change the backup names (to those in the image below) before restoring them using the Maintenance Manager (this utility will not work if the back-up files have been given different names). Back-up created using the People Inc. System Administrator are automatically given these names.

(15)

The Maintenance Manager utility can be used with all versions of SQL Server. The Maintenance Manager utility should be run on the database server (if possible from the console rather than via any remote access software such as Windows Remote Desktop)

To start the Maintenance Manager utility, run PSPMaintenanceManager.exe. There are 2 possible Authentication options. In the first instance, try to use Windows Authentication; this will use your Windows account details to log into SQL Server. If your Windows account does not have the necessary access rights, the SQL Server SA password will be needed to enable you to log in using SQL Server Authentication.

Select ‘Settings>Backup Path’ from the system menu. This path is the location of the backup files. Put a full set of backup files in this folder.

(16)

Confirm that you wish to perform a full-restore by re-typing the 9-digit code generated.

Restoring backups using SQL Server tools

Note that, if you are using a full version of SQL Server (rather than MSDE or SQL Server Express) it is also possible to restore databases using the tools provided with SQL Server. If this approach is taken, care should be taken to ensure that all of the People Inc. databases on the server are restored to a backup taken at a particular point in time (this will comprise 4 or more SQL Server databases). Users are likely to experience problems if a single database backup from a set of backups is restored without the remainder of the set of backups. If backups are restored using SQL Server tools, it may be necessary to create/modify SQL logins manually before People Inc. will function correctly.

SQL Server version

References

Related documents

The above mentioned arguments have been used to determine the factors which influence the adoption of enterprise risk management (ERM) in a firm (e.g. 2011; Golshan and Rashid

Lack of primary storage capacity Lack of automated recovery Insufficient backup tools Lack of enterprise high availability Lack of enterprise storage management Different tools

Income received by shareholders or partners from a holding company or intermediary holding companies which exclusively hold shares in companies owning vessels with the

You can see a summary of the most important things to be remembered. Once completed the Application online you can print the summary with your user name and password and you will

The evidence guide provides advice on assessment and must be read in conjunction with the performance criteria, required skills and knowledge, range statement and the Assessment

Wait while the list will be ready, find and check if State is stable and Last error – no error for Microsoft Exchange Writer... Go to the Exchange System

For these reasons, Norway spruce provenance test and specialized sources and plantations have been established in Serbia to conserve the genetic resources of woody species ex

Lee, ‘The economic policy for the Kim Jong-Un regime in North Korea and the possibility of its change’, Vantage Point: Developments in North Korea, 37 (1) (2014), pp..