Lets cover the loading mechanism to Sybase ASE database. Before loading the dump into the database, we need to ensure that there is no active connection to this database as loading requires to have exclusive access. If exclusive access is not granted, loading process will fail with below error message:
Msg 3101, Level 16, State 1:
Server ‘server_name’, Line 1:
Database in use. A user with System Administrator (SA) role must have exclusive
use of database to run load.
In order to find-out whether currently any user is connected to this database or not, we can execute below:
select * from sysprocesses where db_name(dbid) = ‘db_name’
sp_who
You may use any of above to check. If there is no open session on that database, you may start loading process.
Let’s cover loading database for different types that we have covered so far. First would be to load database from a single file as described in post Improve performance of DB Backup timing by more than 50%
Command will be as below:
use master
go
load database db_name from “/path/db_name_dump_nostripe.bak”
go
online database db_name
go
Please note that, Sybase will not bring database online by itself, we need to bring it online explicitly as per our second command.
Now, let’s see the loading command for the backup which was taken with stripe.
use master
go
load database db_name from
“/path/db_name_dump_stripe_1.bak” stripe on
“/path/db_name_dump_stripe_2.bak” stripe on
“/path/db_name_dump_stripe_3.bak” stripe on
“/path/db_name_dump_stripe_4.bak”
go
online database db_name
go
Issuing first command to change database context to master is not mandatory however it falls under best practice, this becomes handy when you are issuing commandline from isql utility and mistakenly chosen -D option with the database, you are going to load. you may also incorporate sysdate command to check the duration of loading process.
Lets cover loading of database with compress option along with stripe, this is covered in the post Backup compression – save storage 85%(up to) without compromising duration
use master
go
load database db_name from
“compress::5::/path/db_name_dump_comp_1.bak” stripe on
“compress::5::/path/db_name_dump_comp_2.bak” stripe on
“compress::5::/path/db_name_dump_comp_3.bak” stripe on
“compress::5::/path/db_name_dump_comp_4.bak”
go
online database db_name
go
As mentioned earlier, compress option is deprecated and is supported only for compatibility purpose and hence we should avoid it.
Let’s cover loading dump from files which was dumped with compression level. This topic is also covered in detail in post All About Dump Compression. Command to load database taken using compress level would be as follows:
use master
go
load database db_name from
“/path/db_name_dump_stripe_1.bak” stripe on
“/path/db_name_dump_stripe_2.bak” stripe on
“/path/db_name_dump_stripe_3.bak” stripe on
“/path/db_name_dump_stripe_4.bak”
go
online database db_name
go
Here compression level will be detected automatically by Sybase engine, you don’t need to specify it explicitly and this is valid for any compression level, be it 1, 3, 9, 100 or 101.
I have covered loading of database in my video:
Please leave your comment if you liked this post or have any feedback.