From: elton@sybase.com (Elton Wildermuth)
Newsgroups: comp.databases.sybase
Subject: Re: DD vs DUMP DATABASE
Date: 17 Dec 92 21:16:39 GMT
Organization: Sybase, Inc.

Regarding using dd to dump a database:
>
>    A short time ago, someone posted about a customer who had saved their
>database using 'dd' instead of 'dump database.'  I am currently trying to
>help a customer who has decided that this is a good idea.

Ahem.  Two things about this.

First:  Sybase DOES NOT recommend this practice.  As a support engineer,
I "helped" far too many customers who thought that would be the way to go.
The nature of my "help" was to identify the corrupted databases so that
they could set about recreating them from the ground up.  Worse, the
corruption is not limited to the single database that was backed up; it
extends to every database that shares an OS file with that database.

Second:  Okay, you're going to do it anyway.  Follow these steps, or you
WILL have corruption in your system after you restore, and Sybase CAN NOT
help you recover from it.  DO NOT take this advice as a recommendation
from Sybase; see the preceding paragraph.  Sybase's recommendation is,
"Don't do it!"

    1.	Determine which OS files your database resides on.  You will
	need to copy the lot in order to get a good dump.  Say:

		use master

		select distinct d.phyname
		from sysusages u, sysdevices d, sysdatabases b
		where b.name = <database-name>
		  and b.dbid = u.dbid
		  and u.vstart between d.low and d.high
		  and d.status & 2 = 2

	Write the physical names down.  This is the list of devices you
	will have to copy.

    2.	Determine if any other databases use those devices.  If they do,
	you must repeat step 1 for every other database residing on those
	same devices.  Repeat steps 1 and 2 until you have determined ALL
	devices containing ANY portion of a database residing on a device
	found in ANY REPETITION of step 1.  (In fact, while you're at it,
	why not just "select phyname from sysdatabases where status & 2 = 2",
	and have done with it?)  To do this:

		select b.name
		from sysusages u, sysdevices d, sysdatabases b
		where b.dbid = u.dbid
		  and u.vstart between d.low and d.high
		  and d.phyname in ( <repeat the select from step 1> )
		order by 1

	REMEMBER:  Any device that wasn't in the original list must also
	get copied, along with the devices for all databases on those
	devices.  Repeat until thoroughly sick and tired of the search.

    3.	(OPTIONAL) Checkpoint your databases.  This saves recovery time
	when you restart the server.

    4.	SHUT DOWN THE SQL SERVER.  If you don't do this, I personally
	guarantee data corruption upon recovery.

    5.	Use dd, or tar, or whatever command you like to save the files
	you noted in step 1 above.

    6.	Restart SQL Server.

This should ensure that you copy all the correct devices to save all the
data for every database touched by this procedure.

Why bother?  Because if you don't, after you restore your dump, some
portion of some database will be out of sync with the rest of that
database.

Oh, I forgot:  don't restore the files you dumped if you have subsequently
done an ALTER DATABASE on any of the affected devices, OR on any of the
affected databases.  Otherwise, you will have to go in by hand and revise
master.dbo.sysusages to correct its idea of where the databases are, or it
will report corruption.  Not fun.

Like I said:  why not just select phyname from sysdevices and have done
with it?  Better yet, why not just do it the slow way, through DUMP DATABASE?

Best of luck,
			-- Elton