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.
|