ARKEIA 5 PLUG-IN FOR MICROSOFT SQL SERVER

(1)

A

RKEIA

5 P

LUG

-

IN

FOR

M

ICROSOFT

SQL S

ERVER

(2)

Copyrights

Windows® and Microsoft SQL Server® are either registered trademarks or trade-marks of the Microsoft Corporation in USA and other countries.

Arkeia 1808 Aston Avenue Suite 220 Carlsbad, CA 92008 USA Tel : +1 (760) 431 - 1319 Fax : +1 (760) 602 - 8599 Arkeia SA 31, rue Delizy 93692 Pantin Cedex France Tel : +33 (0)1 48 10 89 89 Fax : +33 (0)1 48 10 89 90 US, Canada, Mexico, South America,

Australia, Pacific Rim, China

Europe, Middle East Africa, and central Asia

(3)

ARKEIA SOFTWARE LICENSE AGREEMENT

In order to preserve and protect its rights within the framework of currently applicable legislation, Arkeia Corporation and Arkeia SA, hereinafter referred to as ARKEIA, does not sell rights to this SOFTWARE, but grants the right to use this SOFTWARE, within the terms of this license agreement, hereinafter referred to as LICENSE AGREEMENT, and expressly retains ownership rights to all ARKEIA SOFTWARE. If you do not agree with all the terms and conditions of this LICENSE AGREEMENT you can obtain a refund by return-ing the SOFTWARE, all its manuals, its documentation and the original sealed license envelope, in salable condition, to the place you obtained them.

1. GRANT OF LICENSE. In return for payment of LICENSE fees included in the cost of the SOFTWARE and your commitment to comply with the terms and conditions of this LICENSE AGREEMENT as well as the limited warranty attached to, ARKEIA, the licenser, grants to you, the LICENSEE, the exclusive and non-transferable right to use the SOFTWARE on a single computer known as the backup server, here in after referred to as the SERVER, and its associated client computers, here in after referred to as CLIENTS, provided that the terms and conditions of the license are complied with.

If the SERVER or CLIENT on which the SOFTWARE is operated constitutes a sys-tem with several users, the LICENSE AGREEMENT shall apply to all such users without incurring additional costs.

ARKEIA reserves all rights that are not expressly granted to the LICENSEE. 2. COPYRIGHT. The beneficiary of the LICENSE is the owner of the magnetic

media, or any other type of media on which the SOFTWARE is initially, or subse-quently, recorded or stored. However, this License is granted on the express condi-tion that ARKEIA retains copyrights to the SOFTWARE recorded on the original media as well as copyrights to all copies made, irrespective of the format and the media of said original media and said copies

This LICENSE does not constitute a sale of the original SOFTWARE or of any copy thereof.

3. REPRODUCTION RESTRICTIONS. This SOFTWARE and the accompanying written materials are protected by copyright. Unauthorized reproduction of the SOFTWARE, including its modification, integration or inclusion in another soft-ware, or of the accompanying written materials is strictly forbidden. The LIC-ENSEE is liable to legal sanctions for any copyright infringement caused or prompted by any breach, on the part of the LICENSEE, of the terms and conditions of this LICENSE AGREEMENT.

Subject to the above-mentioned restrictions, the LICENSEE is authorized to make one (1) backup copy of the SOFTWARE if said SOFTWARE is not copy-protected. - Notice of copyright must appear on the backup copy.

4. RESTRICTIONS OF USE. The LICENSEE is authorized to physically transfer the SOFTWARE from one SERVER to another SERVER on condition that said SOFT-WARE is completely and totally removed from the original SERVER. Electronic transfers of the SOFTWARE from one SERVER to another within a distribution network for the purpose of copying the SOFTWARE or the accompanying written

(4)

reverse engineer, decompile, disassemble or create written materials based on the SOFTWARE, and shall not modify, adapt, translate or write literature based on the written materials without the prior express written consent from ARKEIA.

5. TRANSFER RESTRICTIONS. No person whomsoever shall be authorized to oper-ate this SOFTWARE without the prior express written consent from ARKEIA. Any beneficiary of a transfer thus authorized shall be bound by the terms and conditions of this LICENSE AGREEMENT and the limited warranty attached there. Under no circumstances shall the LICENSEE be entitled to transfer, convey, lease or sell the SOFTWARE, nor shall it be entitled to dispose thereof, in any manner whether tem-porary or permanent, except where otherwise expressly provided for herein.

6. CANCELLATION. This LICENSE AGREEMENT shall remain valid until its can-cellation and shall be canceled, as a right without prior notice by ARKEIA should the LICENSEE fail to comply with the terms and conditions of this LICENSE AGREEMENT. In the event of cancellation, the LICENSEE shall immediately destroy all written materials and all copies of the SOFTWARE, including modified copies, where appropriate.

7. MISCELLANEOUS. This LICENSE AGREEMENT is governed by the laws of the State of California (USA) if the LICENSEE acquired the SOFTWARE in the USA with respect to ARKEIA, their successors and assigns. This LICENSE AGREE-MENT is governed by the laws of the country of France if the LICENSEE acquired the SOFTWARE in any country except the USA, with respect to ARKEIA, their successors and assigns.

If you would like further information on this LICENSE AGREEMENT, please write to: Arkeia Corporation 1808 Aston Avenue Suite 220 Carlsbad, CA 92008 USA WARRANTY *

ARKEIA warrants its SOFTWARE for a period of ninety (90) days as of the date of deliv-ery thereof. This warranty also includes reconditioning or replacing SOFTWARE media. ARKEIA does not warrant and does not enter into any commitments regarding the content of the documentation and the software. ARKEIA further disclaims any implicit warranties tied to the sale of the right to use license of this SOFTWARE with respect to its quality, its results, its merchantability or its suitability for a particular purpose. Consequently, the license to use this SOFTWARE is granted “as is”, without any promise being made. In the event of a defect in the software or in the documentation, the LICENSEE, and not ARKEIA, its dealers, distributors, agents, or employees shall bear all costs needed for ser-vicing, repair or correction.

Under no circumstances shall ARKEIA, or anyone else participating in the design, produc-tion and delivery of this SOFTWARE, be liable for any damages, whether direct, indirect, secondary or incidental, including, but not limited to, damages caused by loss of profit,

(5)

business interruption, loss of information or any other loss, resulting from the use of this SOFTWARE, even if ARKEIA has been informed of the possibility of such damages. Information or advice given verbally or in writing by ARKEIA, its dealers, distributors, agents or employees shall not constitute a warranty, nor affect in any way this warranty, and as such, the recipient shall not in any way depend on any such information or advice. Arkeia and ARKEIA SOFTWARE are registered trademarks (TM) of ARKEIA Software, All Rights Reserved. All other trademarks mentioned in this documentation are the prop-erty of their respective owners.

(6)

(7)

T

ABLE

OF

C

ONTENTS

Copyrights...2

ARKEIA SOFTWARE LICENSE AGREEMENT ...3

WARRANTY * ...4

Platforms...9

How to install the Arkeia plug-in for MS SQL...9

Install the Arkeia plug-in for MS SQL Server with .zip ...9

License ...10

MS SQL Server architecture overview ...10

Recovery models ...11 Simple recovery ...11 Full recovery ...11 Bulk-logged recovery ...11 Backup configuration ...12 Backup strategies ...12

Total database backup ...12

Differential database backup ...12

Incremental database backup ...12

Total file or filegroup ...13

Differential file or filegroup ...13

Hot backup...13

Savepacks for MS SQL ...14

Entire MS SQL server savepack...14

Individual SQL instance savepack...15

Individual database savepack ...15

Filegroup savepack ...15

Individual file savepack...16

MS SQL backups ...16

Server, instance and database backups ...17

Total backup ...17

Incremental backup ...17

Differential backup ...17

Filegroup and file backups...18

Master database backup ...18

Rescue backup ...18

How to restore...19

Restoration prerequisites ...19

Restoration utility arkpmssql-rst.exe ... 19

Common options ...20 List mode ...21 Examples ...21 $> arkpmssql-rst.exe -ld -s charon.fr.arkeia.com / ...21 Restoration procedures ...25 Automatic restoration...25

$> arkpmssql-rst.exe -lf -s charon.fr.arkeia.com /PHOBOS/test ...25

Restore a differential backup of specified time ...26

Restore an incremental backup of specified time ...26

Restore, parameter <time> not specified...27

Restore to a <time> between backups ...27

Restore to a point of failure, transaction log available ...28

Manual database restoration ...28

$> arkpmssql-rst.exe -lf -s charon.fr.arkeia.com /PHOBOS/test ...28

Restore total, differential and incremental database backups, and close ...29

(8)

Restore all filegroups since the last total backup...31

Restore some files, not all files ...31

Restoration with relocation ...32

Restore a database to a different instance ...33

Restore a database, change its name ...34

Restore an entire instance...35

Troubleshooting...36

Error messages ...36

(9)

A

RKEIA

PLUG

-

IN

FOR

MS SQL S

ERVER

This manual explains how to use the Arkeia Plug-in for MS SQL Server. An Arkeia plug-in is an optional add-on for Arkeia which makes a specific task easier to do, or adds some functionality.

The Arkeia plug-in for MS SQL Server enables you to quickly hot backup and recover MS SQL Server databases.

Platforms

This version of the plug-in supports backups of:

• MS SQL Server 7,

• MS SQL Server 2000, and later versions.

Supported platforms are:

• Windows NT4 and NT4 Enterprise Edition, both with Service Pack 5;

• Windows 2000 Server, Advanced Server and Datacenter Server; and

• Windows Server 2003 Standard, Enterprise and Datacenter.

The Arkeia MS SQL Server plug-in can be used with Arkeia Network Backup ver-sion 5.3 or later. It does not work with the free verver-sion Arkeia Light, or Arkeia Server Backup.

Check our website:

http://www.arkeia.com/hotbackup/mssql/

for the latest versions of the plug-in, which are added frequently.

How to install the Arkeia plug-in for MS SQL

The Arkeia plug-in for MS SQL Server is delivered on the Arkeia CD, or can be downloaded from http://www.arkeia.com/download/.

Install the Arkeia plug-in for MS SQL Server with .zip

The installation package for the Arkeia plug-in for MS SQL Server is the file arkpmssql-5.3.x.zip

or a later version. To install the plugin, log in to Windows as Administrator, unzip the package and double-click on the file setup.exe.

(10)

License

The Arkeia plug-in for MS SQL Server requires a separate license to operate cor-rectly. This license can be obtained from Arkeia, see our sales partners web site

http://www.arkeia.com/partners/. Follow the procedure contained in the Arkeia User Manual: Arkeia license management on page 43 to activate the license.

MS SQL Server architecture overview

It is useful to understand the different types of file, and the broad architecture of the MS SQL Server when considering what to back up. Below is an overview of the files which make up the MS SQL Server.

A Windows server can have a number of independent instances of the MS SQL Server, a new instance is created each time you install MS SQL Server on a puter. One instance can be the default instance, identified by the name of the com-puter on which it is running. Other instances are identified by the name of the computer and an instance name <computername>\<instancename>. A default instance is not mandatory, all instances can be named instances.

An SQL instance contains:

• system databases (master, model, tempdb and msdb); and

• user databases

The files contained in an SQL instance can be considered to belong to:

• primary file group, or

MS SQL Server instances master model tempdb msdb

Primary data files .mdf

Secondary data files .ndf Log files .ldf Windows server computer Primary file group _User-defined file groups User databases

Secondary data files .ndf

(11)

• secondary user-defined file groups.

Log files are managed separately from the filegroups.

Recovery models

The recovery model chosen for a database will determine how its files will be backed up. The default recovery model is inherited from the model database, when a new database is created. The recovery model for a user database can be changed. if necessary, to meet changed conditions. The recovery models are:

• simple recovery (in MS SQL Server 7, Truncate log on checkpoint is set to on);

• full recovery (in MS SQL Server 7, Truncate log on checkpoint is set to off); and

• bulk-logged recovery.

Note bulk-logged recovery is not available on MS SQL Server 7.

Simple recovery

The database is recovered to its status as at the most recent backup. The Arkeia backup types for this model are:

• total backup, and

• differential backup,

and only databases can be backed up. Full recovery

The database is recovered to a point of failure. The Arkeia backup types for this model are:

• total database, file and filegroups backup;

• differential database, file and filegroups backup;

• incremental database backup; and

The capability to restore to a specific point in time is provided by incremental backup.

Bulk-logged recovery

This is the same as for full recovery, except that restoration to a specific point in time is not possible. Databases with this recovery model perform better and mini-mize the log space used, for certain operations.

(12)

Backup configuration

Assuming Arkeia Network Backup server is installed and running, and an MS SQL Server has been configured as a backup client, the steps for creating any Arkeia backup are:

Step 1: Create backup storage entities in Arkeia :

a. Tapes, (see Tapes on page 73)

b. Tape pools, (see Tape pools on page 79) c. Drive or drives, (see Tape drives on page 82)

d. Drivepacks and libraries, if you have the hardware (see Drivepacks on page 85 and How to create a tape library on page 198).

Step 2: Create a Savepack to contain one or more trees from /MSSQL

See (see Savepacks on page 87).

Step 3: Create an interactive or periodic total backup

If you are not familiar with Arkeia, read Chapter 7:Interactive backup on page 105 or Chapter 8:Periodic backup on page 117 for general instructions on how to cre-ate and run a manual backup and a scheduled backup.

Backup strategies

These types of backup are possible:

An explanation of the terms total, differential and incremental in relation to back-ups is in the Arkeia User Manual, Backup typeson page 65.

Total database backup

A total database backup will copy the entire database to the backup media. This type of backup will allow you to restore the database to its status at the time the backup was completed.

Differential database backup

A differential database backup can be performed only after a total database backup has been done. Only data which has been changed since the total backup is copied to the backup media. To restore from this type of backup, you will restore:

1. the last total database backup; and 2. the last differential backup. Incremental database backup

Incremental backups can be used only in conjunction with full recovery and bulk-logged recovery database models. An incremental backup records the sequence of

Instance Database Filegroup File

Total " " " "

Differential " " " "

(13)

transactions which have changed the database since the last backup, using a trans-action log facility. To restore from this type of backup, you will restore:

1. the last total backup;

2. the last differential backup, if differential backups are being done; and 3. all the incremental backups since the latest total (or differential backup, if

one exists).

An incremental backup of a full recovery model database allows you to restore a data to a point in time.

Total file or filegroup

Individual files or filegroups in a database can be backed up but only in conjunc-tion incremental backups of the database, and require that the database is either a full recovery model or bulk-logged recovery model. To restore from a total file or filegroup backup, you need:

1. the last total backup; and

2. the database incremental backups which follow it.

Note a total file or filegroup backup must always be followed by an incremental backup of the database.

Differential file or filegroup

A differential file or filegroup backup can be performed only after a total backup of the file or filegroup, and is performed in conjunction with an incremental backup of the database. The files which have been changed since the total backup are flagged, and these files are copied to the backup media.

To restore from this type of backup, you will need to restore: 1. the last total file or filegroup backup;

2. the last differential backup; and

3. all the incremental backups since the last differential backup.

This type of backup requires that the database is either a full recovery model or bulk-logged recovery model.

Hot backup

Backup operations can occur while the database is online and in use. However dur-ing a database backup, creatdur-ing or deletdur-ing database files is not allowed. If a backup is started when one of these operations is in progress, the backup waits for the operation to complete, up to the limit set by the session time-out. If a backup is in progress and one of these operations is attempted, the operation fails and the backup continues.

Using the Arkeia plug-in for MS SQL Server supposes that no other backup system performs any backup on it using the SQL Server backup capabilities. Another backup system may reset the backup flags or truncate the transaction logs, so your Arkeia

!

(14)

Savepacks for MS SQL

When the Arkeia plug-in for MS SQL Server has been successfully installed, a new entry will appear in the Network navigator for this client.

In the Main menu , click on Backup > Savepacks > Browse trees:

Using this plug-in and its entries in the Navigator, you can create interactive and periodic backups of the MS SQL Server database files.

An MS SQL savepack can contain an entire SQL Server computer, and one or more:

• SQL instances;

• databases;

• filegroups; or

• single files

These elements can be selected in the normal Arkeia Network navigator screens. See the Arkeia User Manual: Add a tree through the Navigator on page 91 for an explanation of how it works.

Entire MS SQL server savepack

All of the instances of SQL installed on the server are backed up. The instances are backed up sequentially, as if you added each instance tree to a savepack.

In the example above, the computer win2003.arkeiadev.org/ has been selected for the tree being created. All the instances of MS SQL server on this machine will be backed up when the savepack containing this tree is backed up.

(15)

Individual SQL instance savepack

All of the databases contained in the selected instance will be backed up sequen-tially, as if each database tree was added to the savepack.

In the example above, the named instance INSTANCE_A/ on the computer

win2003.arkeiadev.org/ has been chosen for inclusion in this savepack tree, so all

of the databases in this instance will be backed up. More than one instance can be included in the tree.

An incremental or differential backup of an instance will generate an error message when it reaches the master database. To avoid this situation, force a total backup of the the master database. See Master database backup on page 18.

Individual database savepack

A database can be backed up as an entity. When restored, the entire database is recovered.

In the example above, the databases pubs/ and Northwind/ which are in INSTANCE_A, are selected for inclusion in the savepack.

Filegroup savepack

When a filegroup is selected for a savepack, it is backed up as a single entity. When restored, the entire filegroup is recovered. If you select all the filegroups on a

(16)

more efficient to choose the database in this case, because all the file groups are backed up in one operation.

Individual file savepack

Single files can be selected for inclusion in the savepack. It is more efficient to select the filegroup rather than all the individual files

Do not create nested references in a savepack. A backup of a savepack containing a nested reference will fail.

If you create the savepacks by manually entering the tree pathnames in the Tree

options screen:

Main menu > Backup menu > Savepacks Management > [MS SQL savepack name] >

Manage trees > Options

it is possible to enter, for example, a database tree pathname AND a filegroup tree or file which is contained in the same database:

win2003.arkeiadev.org!MSSQL:/INSTANCE_A/MyDatabase

win2003.arkeiadev.org!MSSQL:/INSTANCE_A/MyDatabase/FGroup1

This is a nested reference, and it is not permitted. If you create the savepack through the Browse trees screen by clicking on the grey boxes, you will be pre-vented by the software from entering a nested reference.

MS SQL backups

Some special steps are needed for various MS SQL backup types, and there are some differences between:

• database backups;

• filegroup and file backups;

• master database backup; and

• database failure recovery backup To define backup:

a. choose the MS SQL Server savepack you wish to backup,

(17)

b. assign a drivepack to the backup, c. assign a tape pool to the drivepack,

d. choose either total or incremental backup type, and e. choose other settings as required.

An Archive backup is the same as a total backup, but has no expiry date.

Server, instance and database backups

You can make a total, differential or incremental backups of one or more MS SQL server, instances on a server or databases. Databases on which incremental or dif-ferential backups are to be done must be of the full or bulk-logged recovery model. Total backup

A total backup is performed in the normal way for other Arkeia backups, described in:

• Chapter 7:Interactive backup on page 105; and

• Chapter 8:Periodic backup on page 117.

The first backup of any server, instance or database must be a total backup. Differ-ential and incremental backups which follow will be based on the last total backup. Incremental backup

Incremental interactive or periodic backups are performed in the normal way for other Arkeia incremental backups. An incremental backup is always based on the last backup done, never on a point in time.

Differential backup

Choose Incremental backup in the Type: field, then add the following parameter in

the Advanced parameters screen. Click on and add the following

parameter by typing into the fields shown:

Property: ARKPMSSQL_DIFFERENTIAL_BACKUP

Value: 1

A value of 0 will cancel the property. An SQL differential backup is always based on the last total backup.

(18)

Filegroup and file backups

Filegroup and file backups are only supported by full and bulk-logged recovery models (see Recovery models on page 11). File and filegroup backups are per-formed in two stages:

1. Make a total or differential backup (either interactive or periodic). Incremen-tal backups file are not permitted.

2. Make an incremental backup of the database containing the filegroup or files, to ensure consistency with the rest of the database.

Master database backup

Only total backups are performed on the master database. If you attempt to make a differential or incremental backup of the master database, you will receive an error message. To avoid the error message, configure the backup to either:

• make a total backup, or

• make no backup at all

of the master database with the following parameter entered in the Advanced

param-eters screen. Click on and add this parameter by typing into the

fields:

Property: ARKPMSSQL_FORCE_MASTER_BACKUP

Value: 0 = skip the master database; no backup performed

1 = force a total backup of the master database.

This property applies only to differential and incremental backups. During a total backup it is ignored, and the master database is backed up irrespective of the set-ting.

Rescue backup

Only full and bulk-logged database models support rescue backups. Rescue back-ups can be made when the device containing the data files (.mdf and .ndf) fails, but only:

• on a user database (not on system databases);

• if the transaction log file is still available; and

• if the SQL server instance is still running.

A rescue backup is configured as a normal Arkeia incremental backup, with the addition of the following parameter, entered in the Advanced parameters screen. Click on and add the this parameter by typing into the fields: Property: ARKPMSSQL_RESCUE_BACKUP

(19)

How to restore

Restorations are managed through the command line utility arkpmssql-rst.exe, which is installed on the computer running the MS SQL server, and can only be run locally.

Databases can be restored automatically. If they were created with the full recovery model and their backup strategy includes incremental backups, they can be

restored to a specific point in time. If you nominate a point in time which is cov-ered by the available backups, the automatic restoration will select the backups and the sequence of restoration required to recover the data.

Files and filegroups must be restored manually, which requires you to select the total and differential or incremental backups and their order of restoration to recon-struct the database.

When restoring an entire instance, the system databases are restored first, then the user databases are restored. Both can be restored automatically if they were created with the correct recovery model and backup strategy.

Restoration prerequisites

To ensure a successful restoration, these prerequisites must be met:

1. The server instance to which the data is being restored must be up and run-ning. If the restoration includes a system database, this may not be possible. System database restoration is explained in Restore an entire instance on page 35.

2. No other program is allowed to connect to the server instance during the res-toration.

Restoration utility arkpmssql-rst.exe

arkpmssql-rst.exe has two distinct modes:

• List mode - provides the information you need to choose from the available backups the files you need to restore particular data.

• Restore mode - used to launch automatic or manual restorations.

To run the arkpmssql-rst.exe utility, at the Windows desktop on the computer host-ing the MS SQL server instances:

click on: Start > Run enter: cmd

click: OK

Enter the arkpmssql-rst.exe utility with appropriate options in the command line window which appears. The syntax is:

arkpmssql-rst.exe <MODE> <MODE_OPTIONS> <COMMON_OPTIONS> <COMPONENT_PATH> Available options are displayed using the following command:

(20)

which yields the following information: usage: arkpmssql-rst [OPTIONS]

-l,--list List backup mode.

-d,--directory List the most recent version of backup under the specified path parameter.

-f,--file List all the backups performed for the specified path parameter.

-g,--all List all the backups performed under the specified path parameter.

-n,--files name Get the SQL name and file path of the specified backup path parameter.

-R,--restore Restoration mode.

-t,--time Restore arkpmssql data at given time (see manual for format).

-i,--backup ID Restore from a backup ID.

-c,--close Close the restoration process of a database. -r,--relocate Relocate to another SQL Server instance.

-N,--file name Set new file name and path separated by a comma (see manual for format).

-b,--database name Restore database under the specified name parameter. -F,--force Force restoration to replace old existing databases files. -s,--server-hostname Server hostname (default: local hostname).

-P,--server-port Server port num (default: Arkeia default port num). -u,--user Arkeia user (default: root user).

-a,--ask-password Arkeia user password (default: empty password). -p,--password Arkeia user password in command line (default: empty password).

-H,--client-hostname Client hostname (default: local hostname). -h,--help Print this message.

-v,--verbose Print transaction information.

There are options specific to list and restore modes, and options common to both modes.

Common options

These options are valid in both list and restore modes:

-s <server_name> Specify the backup server hostname. The default value is the

local hostname. As Arkeia does not support Windows comput-ers as backup servcomput-ers, this default value is never correct, so you must always supply the correct backup server name.

-P <port number> Specify the Arkeia server listening TCP port number. The

default value is the Arkeia default port number (617). Typical Arkeia installations are configured to use the 617 port number, so you generally do not need to use this option.

-u <user name> Specify the Arkeia user name with which you want to log on.

The default value is the root user.

-p <password> Specify the Arkeia password for the user specified with the -u

option. The default value is the empty password.

(21)

-v Increase the verbosity. This option generates many cryptic debug messages which are of use only to the Arkeia support team and developers. It is best not to use this option.

List mode Syntax:

$> arkpmssql-rst.exe -l <list_MODE_OPTIONS> <COMMON_OPTIONS> <COMPONENT_PATH>

The output from an arkpmssql-rst.exe list mode command is a list of the available backups for a given tree, specified in the <COMPONENT_PATH>. The list mode options are:

-f list all valid backups available for <COMPONENT_PATH>

-d list the most recent version of each component included in <COMPONENT_PATH>

-g list in inverse chronological order all the valid backups available for <COMPONENT_PATH>, and all the available backups under it

-n list the files contained in a backup of <COMPONENT_PATH>, in the format recog-nized by MS SQL Server. It is useful if you want to specify destination file and pathnames for when relocating or renaming databases, and to see which are the primary and which are the secondary files.

Examples

$> arkpmssql-rst.exe -ld -s charon.fr.arkeia.com /

Backup Date Backup ID Size /Instance/Database/Files Group/File Nov 10 2004 14:26:36 4192170c 717 KB /PHOBOS/test/PRIMARY Nov 10 2004 17:42:18 419244ea 653 KB /PHOBOS/test/PRI-MARY/fprimary1

Nov 10 2004 14:48:30 41921c2e 141 KB /PHOBOS/test/fgroup Nov 10 2004 16:58:26 41923aa2 914 KB /PHOBOS/test

Nov 10 2004 18:26:58 41924f62 916 KB /PHOBOS/test2

Nov 10 2004 17:50:17 419246c9 2642 KB /SQLInstance/Northwind Nov 10 2004 17:50:17 419246c9 10 MB /SQLInstance/master Nov 10 2004 17:50:17 419246c9 720 KB /SQLInstance/model Nov 10 2004 17:50:17 419246c9 11 MB /SQLInstance/msdb

(22)

Nov 10 2004 17:50:17 419246c9 1361 KB /SQLInstance/pubs

$> arkpmssql-rst.exe -ld -s charon.fr.arkeia.com /PHOBOS/test/PRIMARY

Backup Date Backup ID Size /Instance/Database/Files Group/File Nov 10 2004 14:26:36 4192170c 717 KB /PHOBOS/test/PRIMARY Nov 10 2004 17:42:18 419244ea 653 KB /PHOBOS/test/PRI-MARY/fprimary1

$> arkpmssql-rst.exe -lf -s charon.fr.arkeia.com /PHOBOS/test2

Backup Date Backup ID Size Backup type Nov 10 2004 18:48:29 4192546d 17 KB Incremental Nov 10 2004 18:48:14 4192545e 82 KB Incremental Nov 10 2004 18:47:54 4192544a 404 KB Differential Nov 10 2004 18:40:19 41925283 17 KB Incremental Nov 10 2004 18:40:04 41925274 82 KB Incremental Nov 10 2004 18:26:58 41924f62 916 KB Full

$> arkpmssql-rst.exe -lg -s charon.fr.arkeia.com /PHOBOS/Base1

Getting information from charon.fr.arkeia.com...

(23)

Nov 18 2004 16:55:07 419cc5db 82 KB Incr /PHOBOS/Base1 Nov 18 2004 16:54:37 419cc5bd 79 KB Diff

/PHO-BOS/Base1/FGroup1/File3

Nov 18 2004 16:54:37 419cc5bd 79 KB Diff /PHO-BOS/Base1/FGroup1/File4

Nov 18 2004 16:54:19 419cc5ab 79 KB Diff /PHO-BOS/Base1/FGroup1/File2

Nov 18 2004 16:54:19 419cc5ab 79 KB Diff /PHOBOS/Base1/PRI-MARY/File1

Nov 18 2004 16:54:02 419cc59a 143 KB Diff /PHOBOS/Base1/PRI-MARY/PFile

Nov 18 2004 15:02:21 419cab6d 82 KB Incr /PHOBOS/Base1 Nov 18 2004 15:02:02 419cab5a 79 KB Full

/PHO-BOS/Base1/FGroup1/File3

Nov 18 2004 15:02:02 419cab5a 79 KB Full /PHO-BOS/Base1/FGroup1/File4

Nov 18 2004 14:58:27 419caa83 79 KB Full /PHO-BOS/Base1/FGroup1/File2

Nov 18 2004 14:58:27 419caa83 79 KB Full /PHOBOS/Base1/PRI-MARY/File1

Nov 18 2004 14:58:08 419caa70 655 KB Full /PHOBOS/Base1/PRI-MARY/PFile

$> arkpmssql-rst.exe -ln -s charon.fr.arkeia.com /SQLInstance/MYDB

Getting file list for backup ID 41d27500...OK File name File type File path

MYDB_Primary Primary c:\Program Files\Microsoft SQL Server\MSSQL$SQLInstance\data\my_database.mdf

(24)

MYDB_log Log c:\Program Files\Microsoft SQL Server\MSSQL$SQLInstance\data\my_database.ldf

MYDB_Secondary_1 Secondary c:\Program Files\Microsoft SQL Server\MSSQL$SQLInstance\data\my_database_1.ndf

MYDB_Secondary_2 Secondary c:\Program Files\Microsoft SQL Server\MSSQL$SQLInstance\data\my_database_2.ndf

Restore mode Syntax:

$> arkpmssql-rst.exe -R <RESTORE_MODE_OPTIONS> <COMMON_OPTIONS> <COMPONENT_PATH> The restore mode options are:

-t <time> The date and time to which you want the component restored.

The format is: <yyyy><mm><dd><hh><mm><ss>

For example, the 22nd of August 1977, at 7:00:00 PM would

be 19770822190000. The default value is the current date, which

implies that the latest available backup will be restored.

-i <backup id> Restore the specified <COMPONENT_PATH> from the specified

backup ID. Used only in manual restoration procedures. -c Close the restoration process of a database. While the

restora-tion process is not closed the database is not usable in this intermediate, nonrecovered state. When the restoration proce-dure is closed, no other restoration can be performed on the database without restarting the whole procedure. Used only in manual restoration procedures and on the <COMPONENT_PATH> of a database.

-N <name>, <path> Specifies the path to which the backed up files will be

restored. The list of files is obtained using the -n option in list mode.

-r <instance> Restore a database, a filegroup or a file by relocation to

another SQL Server instance. By default, if the -N option is not specified, files are restored in the same directory as the master database primary file in the new (relocated) instance, with their original names.

-b <database> Restore a database, a filegroup or a file to a new database

name. (can be used with the relocation -r flag). By default, if the -N option is not specified, files are restored in the same directory as the master database primary file in the new (relo-cated) instance, and are renamed as follows:

- the primary file becomes <database_name>_Data.mdf - the transaction log file becomes <database_name>_Data.ldf

(25)

- the secondary files become <database_name><id>_Data.ndf where <id> corresponds to the real id value of the files for SQL Server.

Restoration procedures

Automatic restoration allows you to select the point in time to which a database is to be restored. Arkeia will find the relevant backups and restore them in the correct order to recover the database. Manual restoration requires you to choose the required backups and restore them in the correct order. Automatic restoration is available only for databases. See Recovery models on page 11 and Backup strate-gies on page 12 for details.

Automatic restoration

Automatic restoration is started when arkpmssql-rst.exe is entered without the -i or -c options. Using the <time> parameter, Arkeia will find the required sequence of backups to restore the specified database. If no <time> parameter is given, the most recent backup is restored. In the examples that follow, we will restore from a set of backups made from the server charon.fr.arkeia.com of the SQL instance

/PHOBOS/test. To see which backup files are available, we list them using the -f

option with arkpmssql-rst.exe:

$> arkpmssql-rst.exe -lf -s charon.fr.arkeia.com /PHOBOS/test Backup Date Backup ID Size Backup type

Nov 16 2004 10:43:41 4199cbcd 80 KB Incremental Nov 16 2004 10:43:05 4199cba9 402 KB Differential Nov 16 2004 10:42:22 4199cb7e 15 KB Incremental Nov 16 2004 10:41:59 4199cb67 80 KB Incremental Nov 16 2004 10:41:22 4199cb42 914 KB Full

Nov 16 2004 10:40:58 4199cb2a 15 KB Incremental Nov 16 2004 10:39:58 4199caee 80 KB Incremental Nov 16 2004 10:39:37 4199cad9 402 KB Differential Nov 16 2004 10:38:44 4199caa4 15 KB Incremental Nov 16 2004 10:38:28 4199ca94 80 KB Incremental Nov 16 2004 10:35:22 4199c9da 914 KB Full

(26)

This case is applicable to all recovery models. From the list above we chose the backup id 4199c9da to restore, but we used its date and time to make

arkpmssql-rst.exe restore this total backup:

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -t 20041116103522 /PHOBOS/test Full Backup found, Date = Nov 16 2004 10:35:22, ID = 4199c9da

Running the restoration process... Restoring backup ID 4199c9da...OK End of restoration

Closing the database /PHOBOS/test...OK

Restore a differential backup of specified time

This case is applicable to full and bulk-logged recovery models:

Diff Backup found, Date = Nov 16 2004 10:39:37, ID = 4199cad9 Running the restoration process...

Restoring backup ID 4199c9da...OK Restoring backup ID 4199cad9...OK End of restoration

Restore an incremental backup of specified time

This case is applicable to full and bulk-logged recovery models. First, the most recent total backup older than <time> is restored. Next, the last differential backup (if any) between the total backup just restored and <time> will be restored. Finally, all the incremental backups between the backup just restored and <time> will be restored in chronological order:

Incr Backup found, Date = Nov 16 2004 10:38:28, ID = 4199ca94 Incr Backup found, Date = Nov 16 2004 10:38:44, ID = 4199caa4 Running the restoration process...

Restoring backup ID 4199c9da...OK Restoring backup ID 4199ca94...OK Restoring backup ID 4199caa4...OK End of restoration

Diff Backup found, Date = Nov 16 2004 10:39:37, ID = 4199cad9 Incr Backup found, Date = Nov 16 2004 10:39:58, ID = 4199caee Incr Backup found, Date = Nov 16 2004 10:40:58, ID = 4199cb2a Running the restoration process...

Restoring backup ID 4199c9da...OK Restoring backup ID 4199cad9...OK Restoring backup ID 4199caee...OK Restoring backup ID 4199cb2a...OK End of restoration

(27)

Restore, parameter <time> not specified

This case is applicable to full and bulk-logged recovery models, and simple recov-ery models which have only total backups. First, the most recent total backup is restored. Next, the last differential backup (if any) between the total backup just restored and now will be restored. Finally, all the incremental backups from the backup just restored up to now will be restored in chronological order:

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com /PHOBOS/test Full Backup found, Date = Nov 16 2004 10:41:22, ID = 4199cb42 Diff Backup found, Date = Nov 16 2004 10:43:05, ID = 4199cba9 Incr Backup found, Date = Nov 16 2004 10:43:41, ID = 4199cbcd Running the restoration process...

Restoring backup ID 4199cb42...OK Restoring backup ID 4199cba9...OK Restoring backup ID 4199cbcd...OK End of restoration

Restore to a <time> between backups

This case is applicable only to full recovery models. The most recent full backup older than <time> will be restored first. Then the last differential backup (if any) between the full backup just restored and <time> will be restored. Finally, from the backup just restored, all incremental backups in chronological order, up to the first one that was launched after <time> are restored. If an incremental backup newer than <time> exists, then only the transactions that occured from the beginning of this incremental to <time> will be replayed. So at the end of the restore, the data-base will be exactly as it was at <time>:

Incr Backup found, Date = Nov 16 2004 10:38:28, ID = 4199ca94 Incr Backup found, Date = Nov 16 2004 10:38:44, ID = 4199caa4 Incr Backup found, Date = Nov 16 2004 10:39:58, ID = 4199caee Running the restoration process...

Restoring backup ID 4199c9da...OK Restoring backup ID 4199ca94...OK Restoring backup ID 4199caa4...OK Restoring backup ID 4199caee...OK End of restoration

Diff Backup found, Date = Nov 16 2004 10:39:37, ID = 4199cad9 Incr Backup found, Date = Nov 16 2004 10:39:58, ID = 4199caee Incr Backup found, Date = Nov 16 2004 10:40:58, ID = 4199cb2a Incr Backup found, Date = Nov 16 2004 10:41:59, ID = 4199cb67 Running the restoration process...

Restoring backup ID 4199c9da...OK Restoring backup ID 4199cad9...OK

(28)

Restoring backup ID 4199cb67...OK End of restoration

Restore to a point of failure, transaction log available

First, do a rescue backup (see Rescue backup on page 18). This will be an incre-mental backup, obviously the last in your backup sequence.

Note: Relocation during restoration is not possible with a rescue backup.

Next, restore without the <time> parameter (see Restore, parameter <time> not specified on page 27).

In a sequence of backups that does not include any incremental backup, the resto-ration to a point in time which does not correspond exactly to an existing backup is not possible. arkpmssql-rst.exe will then find the last backup performed before the time specified, and restore it.

In a sequence of backups configured with the bulk-logged recovery model, even if it contains incremental backups, restoration is only possible to the exact time of an existing backup.

Manual database restoration

When you restore manually, you first identify the backups to restore by their Backup ID, which you will see when you run arkpmssql-rst.exe in list mode. You need to consider carefully the sequence of restoration so as not to overwrite newer data with old data, or omit data. When all desired restorations are completed, the data-base must be returned to an operational state using the -c option of

arkpmssql-rst.exe.

In the examples that follow, we will restore from the same set of backups we used for the automatic restorations, made from the server charon.fr.arkeia.com of the SQL instance /PHOBOS/test. To see which backup files are available, we list them using the -f option with arkpmssql-rst.exe:

$> arkpmssql-rst.exe -lf -s charon.fr.arkeia.com /PHOBOS/test Backup Date Backup ID Size Backup type

Nov 16 2004 10:43:41 4199cbcd 80 KB Incremental Nov 16 2004 10:43:05 4199cba9 402 KB Differential Nov 16 2004 10:42:22 4199cb7e 15 KB Incremental Nov 16 2004 10:41:59 4199cb67 80 KB Incremental Nov 16 2004 10:41:22 4199cb42 914 KB Full

Nov 16 2004 10:40:58 4199cb2a 15 KB Incremental

(29)

Nov 16 2004 10:39:58 4199caee 80 KB Incremental Nov 16 2004 10:39:37 4199cad9 402 KB Differential Nov 16 2004 10:38:44 4199caa4 15 KB Incremental Nov 16 2004 10:38:28 4199ca94 80 KB Incremental Nov 16 2004 10:35:22 4199c9da 914 KB Full

Restore a database total backup and close it

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 4199c9da -c /PHOBOS/test Full database backup found, Date = Nov 16 2004 10:35:22, ID = 4199c9da Running the restoration process...

Restoring backup ID 4199c9da...OK End of restoration

Restore total, differential and incremental database backups, and close $> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 4199c9da /PHOBOS/test

Full database backup found, Date = Nov 16 2004 10:35:22, ID = 4199c9da Running the restoration process...

Restoring backup ID 4199c9da...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 4199cad9 /PHOBOS/test Differential database backup found, Date = Nov 16 2004 10:39:37, ID = 4199cad9 Running the restoration process...

Restoring backup ID 4199cad9...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 4199caee /PHOBOS/test Incremental database backup found, Date = Nov 16 2004 10:39:58, ID = 4199caee Running the restoration process...

Restoring backup ID 4199caee...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -c /PHOBOS/test Closing the database /PHOBOS/test...OK

Restore total and incremental database backups at <time>

This case is only applicable to databases with incremental backups configured with a full recovery model.

> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 4199c9da /PHOBOS/test Full database backup found, Date = Nov 16 2004 10:35:22, ID = 4199c9da Running the restoration process...

(30)

End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 4199ca94 -t 20041116103700 -c /PHOBOS/test

Incremental database backup found, Date = Nov 16 2004 10:38:28, ID = 4199ca94 Running the restoration process...

Restoring backup ID 4199ca94...OK End of restoration

File or filegroup restoration

The restoration procedures for files and filegroups are identical. The examples that follow are based on this tree segment:

Using the arkpmssql-rst.exe utility, the current list of backups is found: $> arkpmssql-rst.exe -lg -s charon.fr.arkeia.com /PHOBOS/Base1

Getting information from charon.fr.arkeia.com...

Backup Date Backup ID Size Type /Instance/Database/Files Group/File Nov 18 2004 18:46:06 419cdfde 17 KB Incr /PHOBOS/Base1

Nov 18 2004 18:43:09 419cdf2d 82 KB Incr /PHOBOS/Base1

Nov 18 2004 18:42:49 419cdf19 207 KB Diff /PHOBOS/Base1/PRIMARY Nov 18 2004 18:41:54 419cdee2 207 KB Diff /PHOBOS/Base1/FGroup1 Nov 18 2004 18:41:32 419cdecc 82 KB Incr /PHOBOS/Base1

Nov 18 2004 18:41:06 419cdeb2 719 KB Full /PHOBOS/Base1/PRIMARY Nov 18 2004 18:40:50 419cdea2 207 KB Full /PHOBOS/Base1/FGroup1

Note: Each sequence of filegroup backups must be followed by an incremental database backup, otherwise filegroup restoration will not be possible.

(31)

Restore all filegroups since the last total backup

This case could arise if the database was no longer present on the server, or the transaction log file was lost or corrupted.

First, the primary filegroup is restored. Use the list mode option -n to see which is the primary file group.

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdeb2 /PHOBOS/Base1/PRIMARY Full file group backup found, Date = Nov 18 2004 18:41:06, ID = 419cdeb2

Running the restoration process... Restoring backup ID 419cdeb2...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdf19 /PHOBOS/Base1/PRIMARY Differential file group backup found, Date = Nov 18 2004 18:42:49, ID = 419cdf19 Running the restoration process...

Restoring backup ID 419cdf19...OK End of restoration

- restoring a secondary filegroup:

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdea2 /PHOBOS/Base1/FGroup1 Full fgroup backup found, Date = Nov 18 2004 18:40:50, ID = 419cdea2

Running the restoration process... Restoring backup ID 419cdea2...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdee2 /PHOBOS/Base1/FGroup1 Differential file group backup found, Date = Nov 18 2004 18:41:54, ID = 419cdee2 Running the restoration process...

Restoring backup ID 419cdee2...OK End of restoration

Now the sequence of incremental database backups is restored, and the database is closed.

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdf2d /PHOBOS/Base1 Differential file group backup found, Date = Nov 18 2004 18:43:09, ID = 419cdf2d Running the restoration process...

Restoring backup ID 419cdf2d...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdfde -c /PHOBOS/Base1 Differential file group backup found, Date = Nov 18 2004 18:46:06, ID = 419cdfde Running the restoration process...

Restoring backup ID 419cdfde...OK End of restoration

Restore some files, not all files

In this case, part of the database is present, and the transaction log file is intact. We will restore the filegroup FGroup1. First, perform a rescue backup of the database. A

(32)

$> arkpmssql-rst.exe -lg -s charon.fr.arkeia.com /PHOBOS/Base1 Getting information from charon.fr.arkeia.com...

Backup Date Backup ID Size Type /Instance/Database/Files Group/File

Nov 18 2004 19:01:09 419cdfee 17 KB Incr /PHOBOS/Base1 Nov 18 2004 18:46:06 419cdfde 17 KB Incr /PHOBOS/Base1 Nov 18 2004 18:43:09 419cdf2d 82 KB Incr /PHOBOS/Base1

Nov 18 2004 18:42:49 419cdf19 207 KB Diff /PHOBOS/Base1/PRIMARY Nov 18 2004 18:41:54 419cdee2 207 KB Diff /PHOBOS/Base1/FGroup1 Nov 18 2004 18:41:32 419cdecc 82 KB Incr /PHOBOS/Base1

Nov 18 2004 18:41:06 419cdeb2 719 KB Full /PHOBOS/Base1/PRIMARY Nov 18 2004 18:40:50 419cdea2 207 KB Full /PHOBOS/Base1/FGroup1

Next, restore the filegroup FGroup1.

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdea2 /PHOBOS/Base1/FGroup1 Full fgroup backup found, Date = Nov 18 2004 18:40:50, ID = 419cdea2

Running the restoration process... Restoring backup ID 419cdea2...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdee2 /PHOBOS/Base1/FGroup1 Differential file group backup found, Date = Nov 18 2004 18:41:54, ID = 419cdee2 Running the restoration process...

Restoring backup ID 419cdee2...OK End of restoration

Last, restore the sequence of incremental backups, including the rescue backup, and close the database.

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdf2d /PHOBOS/Base1 Differential file group backup found, Date = Nov 18 2004 18:43:09, ID = 419cdf2d Running the restoration process...

Restoring backup ID 419cdf2d...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdfde /PHOBOS/Base1 Differential file group backup found, Date = Nov 18 2004 18:46:06, ID = 419cdfde Running the restoration process...

Restoring backup ID 419cdfde...OK End of restoration

$> arkpmssql-rst.exe -R -s charon.fr.arkeia.com -i 419cdfee -c /PHOBOS/Base1 Differential file group backup found, Date = Nov 18 2004 19:01:09, ID = 419cdfee Running the restoration process...

Restoring backup ID 419cdfee...OK End of restoration

Closing the database /PHOBOS/test...OK Restoration with relocation

A database, filegroup or file from one instance of SQL Server can be restored to a different instance, or restored with a different name. Also, data backed up on an older version of MS SQL Server can be restored to a later version.

(33)

Note: Relocation during restoration is not possible with a rescue backup.

Restore a database to a different instance

In this case, a database is restored to a different instance. The restoration can be automatic or manual, and uses the -r <instance> option. If the -N <file>,<path> option is not specified, by default files are restored to their original names in the same directory as the master database primary file of the instance to which they are being relocated. In this example, we will restore from the following sequence of backups:

>$ arkpmssql-rst -s charon.fr.arkeia.com -lf /INSTANCE1/pubs Backup Date Backup ID Size Backup type Jan 03 2005 10:59:46 41d91792 80 KB Incremental Jan 03 2005 10:59:21 41d91779 850 KB Full

Restore this sequence of backups to the instance INSTANCE2 with default filenames: $> arkpmssql-rst -s charon.fr.arkeia.com -R -r INSTANCE2 /INSTANCE1/pubs

Full Backup found, Date = Jan 03 2005 10:59:21, ID = 41d91779 Incr Backup found, Date = Jan 03 2005 10:59:46, ID = 41d91792 Running the restoration process...

Retrieving informations on backup ID 41d91779...OK Restoring backup ID 41d91779...OK

Restoring backup ID 41d91792...OK End of restoration

Closing the database /INSTANCE2/pubs...OK

Restore the same sequence with new filenames. First, get the names of files included in the full backup: $> arkpmssql-rst -s charon.fr.arkeia.com -ln /INSTANCE1/pubs Getting file list for backup ID 41d91779...OK File name File type File path

pubs_Data Primary C:\Program Files\Microsoft SQL

Server\MSSQL$INSTANCE1\data\pubs_Data.MDF pubs_Log Log C:\Program Files\Microsoft SQL

Server\MSSQL$INSTANCE1\data\pubs_Log.LDF Next, restore with new filenames:

$> arkpmssql-rst -s charon.fr.arkeia.com -R -r INSTANCE2

-N pubs_Data,"C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCE2\data\pubs_Data.MDF" -N pubs_Log,"C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCE2\data\pubs_Log.LDF" /INSTANCE1/pubs

Restoring backup ID 41d91779...OK Restoring backup ID 41d91792...OK End of restoration

Closing the database /INSTANCE2/pubs...OK

(34)

Restore a database, change its name

A database, filegroup or file backup can be restored to a database of a different name. By default, if the -N <file>,<path> option is not specified, files are restored to the same directory as the instance’s master database primary file. Then, filena-mes will be generated automatically, using the new database name and the normal file extensions (primary files- .mdf, secondary files- .ndf, and log files- .ldf). In this example, we will restore from the following sequence of backups:

>$ arkpmssql-rst -s charon.fr.arkeia.com -lf /INSTANCE1/pubs Backup Date Backup ID Size Backup type Jan 03 2005 10:59:46 41d91792 80 KB Incremental Jan 03 2005 10:59:21 41d91779 850 KB Full

We will restore this sequence of backups to the database pubs_renamed, with the default file names.

$> arkpmssql-rst -s charon.fr.arkeia.com -R -b pubs_renamed /INSTANCE1/pubs Full Backup found, Date = Jan 03 2005 10:59:21, ID = 41d91779

Incr Backup found, Date = Jan 03 2005 10:59:46, ID = 41d91792 Running the restoration process...

Retrieving informations on backup ID 41d91779...OK Restoring backup ID 41d91779...OK

Restoring backup ID 41d91792...OK End of restoration

Closing the database /INSTANCE1/pubs_renamed...OK

Here, we restore the same sequence to the instance INSTANCE2 with new filenames. First, list mode is used to get the names of the files included in the full backup. $> arkpmssql-rst -s charon.fr.arkeia.com -ln /INSTANCE1/pubs

Getting file list for backup ID 41d91779...OK File name File type File path

pubs_Data Primary C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCE1\data\pubs_Data.MDF

pubs_Log Log C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCE1\data\pubs_Log.LDF

Next, files are restored with new filenames.

$> arkpmssql-rst -s charon.fr.arkeia.com -R -b pubs_renamed -N pubs_Data,"C:\Program Files\Microsoft SQL

Server\MSSQL$INSTANCE1\data\pubs_renamed_Data.MDF" -N pubs_Log,"C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCE1\data\pubs_renamed_Log.LDF" /INSTANCE1/pubs

Restoring backup ID 41d91779...OK Restoring backup ID 41d91792...OK End of restoration

Closing the database /INSTANCE1/pubs_renamed...OK

You can run a restore which relocates and renames a database, and changes the filenames, at the same time.

(35)

Restore an entire instance

If backups are available for all the databases of an instance, it is possible to restore an entire instance with the following steps.

Step 1: Create a new instance

Create a new instance of the MS SQL Server with the same name as the one you want to restore.

Step 2: Start the new SQL instance

Start the new MS SQL Server instance in single-user mode. To do this, at a com-mand prompt, switch to the appropriate directory. This will be:

• for the default instance:

C:\Program Files\Microsoft SQL Server\MSSQL\Binn

• for a named instance:

C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCE_NAME\Binn Then, to start the instance, enter:

• for the default instance: $> sqlservr.exe -c -m

• for a named instance:

$> sqlservr.exe -c -m -s INSTANCE_NAME

Step 3: Restore the master database

See Automatic restoration on page 25 or Manual database restoration on page 30.

Step 4: Stop the SQL server

At the command prompt, press Ctrl+c twice to stop the SQL server in single-user mode.

Step 5: Restart the SQL server in normal mode Step 6: Restore the other system databases

Restore the model and msdb databases from the total, differential and incremental backups.

Step 7: Restore the user databases

Restore from the total, differential and incremental baskups.

Further information about the restoration of system databases can be found at:

(36)

Troubleshooting

Not all error messages you receive will be directly connected with the Arkeia MS SQL Server plug-in. The messages listed below are those which may arise during the use of the plug-in.

Error messages

Error message Meaning or Solution

SQL Server does not exist or

access denied The SQL instance you tried to con-nect to is probably down.

Connection not open The SQL instance you tried to

con-nect to is probably down.

Only full backup can be performed on a database configured in sim-ple recovery model, change recov-ery model of your database in SQL Server to Full or Bulk-logged.

Changes to the recovery model must be performed directly on the database configuration from within the SQL Server.

Only total backup is allowed on the 'master' database, performs a total backup or use the

ARKPMSSQL_FORCE_MASTER_BACKUP parameter.

See Master database backup on page 18

Perform a rescue backup of your database first. If you cannot, remove the log file of the data-base, but last transactions not backed up will be lost.

Usually, this error happens when you try to restore a file or a file-group after a device failure, without having backed up the transaction log by performing a rescue backup (see Rescue backup on page 18). If rescue backup is not possible, the last solution is to manually remove the database log file from the SQL Server machine.

Restore the primary file first You have begun to restore your

database by a file which is not the primary file or a file group which does not contain the primary file. To know where primary and sec-ondary files are, use the list mode with -n option on the file or file group to restore.

The database you tried to restore is already closed. The restora-tion process must be restart from the last full backup without closing it, and then you perform this restoration again.

This may arise during a manual restoration.

(37)

Warning messages

One or more database and/or log file(s) already exist(s). Force the restoration with the -F option, or remove file(s) manu-ally.

The plug-in will not permit the destruction of an existing file by overwriting with a new file of the same name. Use the -F option to overwrite files. Note: If the file is owned by a running database of SQL Server, overwrite will not be possible.

Warning message Meaning

After files and/or filegroups backup, an incremental database backup must be performed, Other-wise files and/or filegroups can-not be restored.

Each sequence of filegroups backup MUST be followed by an incremental database (yes, data-base) backup, otherwise filegroup restoration will not be possible.

(38)