Veritas-bu

[Veritas-bu] Alternate Client SQL Restores - Problems!!

2002-10-10 07:07:20
Subject: [Veritas-bu] Alternate Client SQL Restores - Problems!!
From: paul.sobey AT aleagroup DOT com (Paul Sobey)
Date: Thu, 10 Oct 2002 12:07:20 +0100
Dear all,

Last week, one of our MSSQL7 servers blew its disk. We had planned for 
some time to migrate the data onto a better class of server hardware, and 
take the opportunity to migrate to Windows 2000/SQL 2000, so decided to 
bring this plan forward, since the alternative was to rebuild the old box.

The new box was duly configured with a different disk structure from the 
old, with databases kept on separate disks from logs. We determined that 
in order to restore the databases we would have to write script files that 
included the 'move' command, to move the database and log locations into 
the new paths. After writing some perl to create the script files using a 
list of images generated with bplist, we discovered that we could only 
restore a fraction of the databases we needed to. 4 days later, after a 
rather painful experience with Veritas support, we realised that the move 
command requires the logical filename of both database and log files, in 
order to work properly.

Unfortunately, the previous server had been configured by people who 
thought it amusing to randomise the logical filenames when creating 
databases, such that they bore no relation whatsoever to either the 
database names, or the physical filenames. We discovered that we couldn't 
restore most of our databases without knowing the logical filenames in 
advance, to properly create the move scripts.

Our next approach was to try and restore the master database from the old 
server, using a different name, to query the logical filenames from the 
sysdatabases table. This didn't work, since the sysdatabases table only 
contains database names and physical filenames - SQL server presumably 
reads the logical names from the individual database files at boot time.

Finally, we have had to rebuild an old server with an identical disk 
configuration to the old, restore all the databases onto it (200 of the 
damned things), then write SQL scripts to determine the logical filenames, 
to issue an alternate client restore on the new server (faster to restore 
from tape then replicate from the old server onto the new!)

There must be a better way than this. So my questions, to those who have 
perservered through my waffle, are:

Is there any way to determine the logical filename of a SQL database from 
the Veritas software in advance of actually restoring it?
Is there any way to restore the raw SQL dump from tape onto a filesystem, 
bypassing the database agent? I ask because I know the dumps are generated 
using a standard SQL backup command, so that if I could get at the file, I 
could determine the logical filename using the SQL dbcc command, then 
restore it myself using the restore command.
Am I missing something else really obvious? It seems that in a DR 
situation, recovering databases to a new server with a differerent disk 
configuration shouldn't be all that strange a thing to do, yet I can't see 
a way of doing it without either knowing the logical filename of the 
database/log or getting the data off tape to query it with SQL server.

Any answers gratefuly received, since I am due for a major grilling from 
an angry manager who wants to know why a server restore has taken several 
days! NT/Domino restores are soooooo much easier, it seems stange that SQL 
ones require this much extra work... I now have a script in place that 
queries all my SQL servers each night and writes out the database names, 
logical and physical filenames, to a text file, so that I won't have to go 
through this pain again!

Best Wishes,
Paul

___________________________________________
CONFIDENTIALITY NOTE: 

The information contained in this email message may be legally privileged and 
contain confidential information  and  is intended only for the use of the 
individual or entity to whom it is addressed.  If the reader of this message is 
not the intended recipient, you are hereby notified that any dissemination, 
distribution or copy of this message is strictly prohibited.  If you have 
received this email in error, please immediately delete this message.  Thank 
you.

<Prev in Thread] Current Thread [Next in Thread>
  • [Veritas-bu] Alternate Client SQL Restores - Problems!!, Paul Sobey <=