We help IT Professionals succeed at work.

View the contents of a database dumps without actually loading the database.

knel1234
knel1234 asked
on
Hey,

I would like to view the contents of a specific table in Sybase database from a previous dump file.  While I know this is possible in oracle (not so much a dump per say but whatever), I thought this was possible in Sybase 12.5.x (12.5.3 12.5.4????).  I dont want to load the dump to an actual database, therefore, please dont answer that question.  In general, I want to dump a database (daily) and then be able to "poke around" the dump and load based on this research.  If you need additional information (or specific details - Note:  Im not looking to "change" this question or ask multiple questions for the "price of one" rather just refine this questions), please let me know.

Thanks
Comment
Watch Question

Principal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
You can run LOAD DATABASE ... FROM "..." WITH LIST_ONLY | HEADER_ONLY

Note you *do* have to have a database that exists to load into, but the size doesn't matter. The database is considered unavailable while the load proceeds so don't use any of your real databases or even tempdb. However a minimum-size dummy database (2, 3 or 5Mb depending on your version) would be fine.

This will give you information like what the name of the database is, when it was dumped, from what server, etc.

If you want to actually be able to poke around (ie view data etc) then you need ASE 12.5.4 or 15.x. You can mount each stripe of the dump as a special kind of device, and create a dummy "archive database" which brings the database online, sorta. You need to define some "scratch" database space (used to hold any writes you make), but effectively you can mount a database dump directly without having to fully load it. The scratch and archive database sizes can be vastly smaller than the real database is.

Read up on "archive databases" in ASE 12.5.4 or 15.0.

This isn't available in earlier versions.

Author

Commented:
Joe,

I was looking for the point you stated in the following section of your answer:

>If you want to actually be able to poke around (ie view data etc) then you need ASE 12.5.4 or 15.x. You can mount each stripe of >the dump as a special kind of device, and create a dummy "archive database" which brings the database online, sorta. You need to >define some "scratch" database space (used to hold any writes you make), but effectively you can mount a database dump directly >without having to fully load it. The scratch and archive database sizes can be vastly smaller than the real database is.

>Read up on "archive databases" in ASE 12.5.4 or 15.0.

I had already downloaded the pdf for this topic.  However, I am struggling with the implementation.  Sybase notoriously stuggles with their documentation and they have done so on this topic.

I have tried

#1)

create database my_scratch_db
on data_1 = 100

sp_dboption my_scratch_db, "scratch database", "true"

I attempted these permutation:
#2a) create archive database on my_test_db
       on data_1
       with scratch_database = my_test_db

#2b) create archive database on my_test_db
       on data_1
       with scratch_database = my_scratch_db

#2c) create archive database on my_scratch_db
       on data_1
       with scratch_database = my_test_db

#2d) create archive database on my_scratch_db
       on data_1
       with scratch_database = my_scratch_db

In their "Create an archive database" section of the documentation, Sybase states that the database arguement in the "create archive database" clause must exist.  However, I am unable to create this "archive" database.  I have attempted to create the archive database via all the possible permutations and no luck.  I guess Sybase doesnt really like specific examples.  Anyhow, I would like to proceed yet I am still stuck.  If I am "out of scope" on this questions, please let me know(I will start another).
I get the concept but specifics would be great.

Thanks
knel

Author

Commented:
Joe et all,

Nevermind I got it.  I had something stuck in my head. That wasnt right.   Thanks for the info.

I should have done:

create database scratchdb on temp_2=20
sp_dboption "scratchdb", "scratch database","true"
use scratchdb
go
checkpoint
create archive database archivedb
on temp_2 = 100
with scratch_database = scratchdb


cheers
knel
Joe WoodhousePrincipal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
I've only done this for real production databases once, so I wasn't quite sure of the exact syntax myself.

Most of the time Sybase gets their docs right but as you say every now and again something needs a bit more attention. Glad you figured it out - the key element is that you need to tell ASE that you're bringing a database online as an archive only, but also what area of disk to use for any writes (that can only be done in scratch).

Explore More ContentExplore courses, solutions, and other research materials related to this topic.