NovaBackup DC
SQL Backup and
Restore
Document version 1.0
Contents
Copyright
3
Overview
5
Initial Preparation on the MS-SQL System
6
Preparation of MS-SQL Backup Jobs
8
The Backup
10
The Restore
11
The information in this publication is subject to change without notice and is provides “AS IS” WITHOUT WARRANTY OF ANY KIND. THE ENTIRE RISK ARISING OUT OF THE USE OF THIS
INFORMATION REMAINS WITH RECIPIENT: IN NO EVENT SHALL NOVASTOR BE LIABLE FOR ANY DIRECT, CONSEQUENTIAL, INCIDENTAL, SPECIAL, PUNITIVE OR OTHER DAMAGES WHATSOEVER (INCLUDING WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION OR LOSS OF BUSINESS INFORMATION), EVEN IF NOVASTOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
The limited warranties for NOVASTOR products are exclusively set forth in the documentation accompanying such products. Nothing herein should be construed as constituting a further or additional warranty.
This publication does not constitute an endorsement of the product or products that were tested. The
configuration or configurations tested or described may or may not be the only available solution. This test is not a determination or product quality or correctness, nor does it ensure compliance with any federal state or local requirements.
Hewlett-Packard, HP, and HP-UX are registered trademarks of Hewlett-Packard Company. IBM and AIX are registered trademarks of IBM Corporation.
Linux is a registered trademark of Linus Torvald.
Microsoft and Windows are registered trademarks of Microsoft Corporation.
Oracle is a registered trademark, Oracle8, and Oracle9 are trademarks or registered trademarks of Oracle Corporation.
SAP, R/3 and other SAP products and services possibly mentioned in this manual as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world.
Sun, Sun Microsystems, Solaris, and Java are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.
UNIX is a registered trademark in the U.S. and other countries, licensed exclusively through X/Open Company, Ltd.
Other product and company names mentioned herein may be trademarks of their respective companies. NOVASTOR believes the information in this publication is accurate as of its publication date; such information is subject to change without notice.
Copyright 2010 NovaStor Software GmbH
This product includes software developed by Vanadyne GmbH, copyright © 2010 Vanadyne GmbH. This product includes software developed by the Apache Software Foundation (http://www.apache.org/).
This product includes software developed by The Cryptix Foundation Limited (http://www.cryptix.org), copyright © 1995-2001 The Cryptix Foundation Limited. All rights reserved.
This product includes software which is redistributed under MOZILLA Public License and NETSCAPE Public License (http:// http://www.mozilla.org).
This product includes software developed by Sun Microsystems, Inc. (http://www.sun.com), copyright © Copyright 2002 Sun Microsystems, Inc., 4150 Network Circle, Santa Clara, California 95054, U.S.A. All rights reserved.
MS-SQL backup and restore with NovaBackup DataCenter can be divided into 4 steps
• Initial preparation on the MS-SQL system • Preparation of MS-SQL backup jobs
• The backup • The restore
Each of these steps is described in one of the following chapters.
The initial task has to be done on the MS-SQL server system. It has to be done only one time, except for the case that databases are added to the MS-SQL server.
Hiback must be installed in beforehand. The Hiback package contains a program called himsdb.exe. This program is able to query the MS-SQL server in order to retrieve basic information like DB names. This
information is used to create DBA files in the Hiback\MS-SQL directory. These files are necessary to define how the DB can be accessed by Hiback in order to perform an online backup.
There are 3 types of DBA files describing DB full backup
DB differential backup DB log backup
Login into the MS-SQL system as a user having access to the MS-SQL server.
Open a DOS box (cmd.exe) and change into the Hiback\MS-SQL directory. Issue the command himsdb -m a
In case the SQL server name is a composition of Servername\Domainname, himsdb has to be called with the –s option: himsdb –s
Servername\Domainname –m a, where the names have to be substituted
accordingly.
Here is an example output:
C:\hiback\MS-SQL>himsdb -m a
Microsoft SQL-Server Agent 07.05.00 (C) 1990-2009 NovaStor GmbH Starting SQL - command . . . .
Starting use master; select name, crdate, filename from sysdatabases Creating Multi-DBA template : WAMD64_all.dba
ODBC connected to WAMD64!
SQLexec: use master; select name, crdate, filename from sysdatabases Msg 5701, SevLevel 0, State 1, SQLState 01000
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'master'. --- DBinfo --- Creating: DATA_WAMD64_master.dba Creating: LOGS_WAMD64_master.dba Creating: TDIF_WAMD64_master.dba Creating: DATA_WAMD64_model.dba Creating: LOGS_WAMD64_model.dba Creating: TDIF_WAMD64_model.dba Creating: DATA_WAMD64_msdb.dba Creating: LOGS_WAMD64_msdb.dba Creating: TDIF_WAMD64_msdb.dba Creating: DATA_WAMD64_Test.dba Creating: LOGS_WAMD64_Test.dba Creating: TDIF_WAMD64_Test.dba Creating: DATA_WAMD64_EFR-EFR2220.dba Creating: LOGS_WAMD64_EFR-EFR2220.dba Creating: TDIF_WAMD64_EFR-EFR2220.dba --- Done with SQLcmd! --- C:\hiback\MS-SQL>
The naming convention for the files is <BackupType>_<ServerName>_<DBName>.dba.
The preparation of the MS-SQL backup jobs is done in the Central Management GUI (ixt_cman). Start the GUI and login as a user having
appropriate permission to create jobs. Click Jobs and place the cursor in the job-list panel (right hand side). Right-click to open the actions dropdown list. Choose NewSingle JobDBA.
Release the mouse, the job-definition window appears.
Fill out the form, begin with jobname and choose the MS-SQL server nodename from the node list.
On the right-hand side click Browse. A filetree showing the filesystem on the MS-SQL server system is shown. The + signs let you open a directory. Browse down to the Hiback\MS-SQL directory, where you can see the DBA files which have been created before.
Choose the DBs as well as the backup type to be backed up:
Click OK when finished.
Choose a media pool as 1st media pool and save the job by clicking OK down right.
The backup can be started interactively as shown in the chapter before or at defined times via the scheduler. To run a scheduled backup, a schedule has to be applied to the job. If you do not have a schedule yet, it must be defined. You can find a description of these actions in the Hiback ixT Operation Manual which is part of the documentation kit.
Point-in-time recovery
We are describing the case of a point in time recovery for one database. A point in time recovery means that a sequence of restores has to be performed beginning with the last FULL, continuing with the last DIFF and following LOG and ending with the last LOG restore. Last means the last backup before the point in time to recover.
For each restore a separate restore DBA file must be created. The name is arbitrary, it will be handed to the GUI in the restore process. The
creation of the DBA files has to be done on the MS-SQL server system locally.
The procedure is to copy the respective DBA files for that database into the Hiback\job directory and to change the HI_OPTION entry in the DBA files. The entry contains syntax which is handed to the MS-SQL RESTORE DATABASE command.
For the FULL, DIFF and all LOG DBA files except for the last the entry is HI_OPTION=”NORECOVERY”
When the last LOG is to recover, the HI_OPTION of the LOG DBA file is changed to
HI_OPTION=”RECOVERY, STOPAT = ‘AUG11, 2009 13:25:45 PM’ “
for example. Novastor offers a commandline tool called pitrestore. Pitrestore has to be started on the MS-SQL server system. It accepts 2 parameters: a database name and a date/time information. The date time information is the point-in-time to recover the MS-SQL DB. Here is an example call for the point-in-time recovery of the DB Test for the 23rd
of Novemer 2009, 14:11:
C:\hiback>pitrestore test "23/11/2009 14:11" The Restore Sequence will be:
Restore Full : 001258977715 = Mon Nov 23 13:01:55 2009 Restore Diff : 001258981490 = Mon Nov 23 14:04:50 2009 Restore Log : 001258981927 = Mon Nov 23 14:12:07 2009 The last Logbackup wants to STOPAT 'Nov 23, 2009 14:11: PM' End of Program
Please start the restore/recovery by executing c:\hiback\job\restore\test\test.cmd
Pitrestore has created the script c:\hiback\job\restore\test\test.cmd which contains the restore commands in the right order. It has prepared the according DBA files which will be used for the restores.
Cross Recovery
A cross recovery can be performed on the original system to create a copy of a database (with different name) or to perform a cross system recovery onto another MS-SQL server system.
A separate restore DBA file has to be created. The name is arbitrary, it will be handed to the GUI in the restore process. The creation of the DBA files has to be done on the MS-SQL server system locally.
The procedure is to copy the respective DBA files for that database into the Hiback\job directory and to change the HI_OPTION entry in the DBA file.
The entry contains syntax which is handed to the MS-SQL RESTORE DATABASE command.
An example entry is
HI_OPTION=”FILE=1,RECOVERY,MOVE 'Northwind' TO 'D:\qa\data\Northwnd2.mdf', MOVE 'Northwind_log' TO 'D:\qa\data\Northwnd2.ldf'”
The restore can be started using the Central Management GUI. Beware to click Overwrite and Wait in the “General options” and to click DBA in “Restore type”. Specify the name of the restore DBA file at “File set definition” as shown below
