Sybase ASE create devices and load database

Sybase Adaptive Server Enterprise (ASE) introduces enhanced dump and load capabilities for moving databases or restoring backups to a different ASE .
It may be confusing for some other database administrators. So, I would like to share with you my experience and give you a brief explanation of the device, database and the restoration of the Sybase database from command line

Let’s start with:

Creating database devices
Creating databases
Loading database dumps

The follow is the procedure to create a device, database and alter database operation.

1- Create the device:

Makes a physical device or file usable by Adaptive Server, Sybase devices can be created on a single disk or across multiple disks. Sybase devices is used during create databases process

login to ASE, on Windows OS at the command prompt type:

isql -Usa -P<password> -S<db server > -X

The following example shows you how to create “data1” and “log1” devices on the physical disk “D:\” for directio.

disk init name=”data1″, physname=”d:\datafile\i1\data1.dat”, size=”100G”,  directio= true

go

disk init name=”log1″, physname=”d:\datafile\i1\log1.dat”, size=”50G” , directio= true

go

For UNIX-LINUX

disk init name = ‘data1′,physname=’/tmp/data1′,size=’100G’
go
disk init name=’log1′,physname=’/tmp/log1′,size=’50G’
go

Note: directio
allows you to configure Adaptive Server to transfer data directly to disk, bypassing the operating system buffer cache.
directio is a static parameter that requires a restart of Adaptive Server to take effect. By default, directio is set to false

2- Create the database:

Now lets create mydatabase1 and mydatabase2 database on the devices created earlier, notice that you can create more than on databases on one device

example:

create database mydatabase1 on data1=’1000M’ log on log1=’800M’ for load
go
create database mydatabase2 on data1=’1000M’ log on log1=’800M’ for load
go

3- Alter the database:

alter database mydatabase1 on data1 = ‘500M’ for load
go
alter database mydatabase1 on log1 = ‘200M’ for load
go

4- Load the database:

load Adaptive Server database  after a series of disk init, create database, and alter database operations completed.

The following example shows you how to restore the database mydatabase1 from stripe backup dump file.

A load can take significantly longer than a dump. The time required to load a database depends on the overall number of pages in the database.

example:

load mydatabase1 from ‘compress::E:\backup\datafile\backup1.bak’

stripe on ‘compress::E:\backup\datafile\backup2.bak’

stripe on ‘compress::E:\backup\datafile\backup3.bak’

go

Hint: you can use “hederonly” to get information from the dump file without actually loading it, like the server platform for the dump file, page size, ASE version and EBF, char set and sort order ID ..etc

load mydatabase1 from ‘compress::E:\backup\datafile\backup1.bak’

stripe on ‘compress::E:\backup\datafile\backup2.bak’

stripe on ‘compress::E:\backup\datafile\backup3.bak’

with headeronly

go

The following example for UNIX-LINUX

load mydatabase1 from ‘compress::/opt/backup/datafile/backup1.bak’

stripe on ‘compress:: /opt/backup/datafile/backup2.bak’

stripe on ‘compress:: /opt/backup/datafile/backup3.bak’

go

Execute the following to bring the database online.
Make sure to load the database dump and all of the transaction logs before bringing the database online.

online database mydatabase1
go

execute the following to check database status

sp_helpdb mydatabase1
go

use mydatabase1
go

Bonus : You can create a many database device and create a database on multiple device

example:

disk init name=’data1′,physname=’/tmp/data1′,size=’50G’
go
disk init name=’data2′,physname=’/tmp/data2′,size=’50M’
go
disk init name=’log1′,physname=’/tmp/log1′,size=’50G’
go

create database mydatabase1 on data1=’1000M’, data2=’500M’ log on log1=’800M’ for load
go
alter database mydatabase1 on data1 = ’50M’
go
alter database mydatabase1 log on log1 = ’20M’
go

For more info sybase.com