This is day 5 of my training and today it is going to focus on Backups, Advanced Backup Techniques, Sybase Central and Monitoring the System
Backups
Instead of backup and restore Sybase uses the commands dump and load, SQL Server people will be familiar with dump devices and the sp_addumpdevice stored procedure. In order to be able to perform backup, you first need to make sure that the Backup Server is running. You also need to add a dump, device, below is some sample syntax
sp_addumpdevice "disk", "db_dump_device",
"/home/usr/u/someserver/sa/db_dump_device.dat"
Database backups
This is pretty much the same as SQL Server, it is a full database backup. Sybase does not have differential backups, Sybase has incremental backups and this is just a log backup. Sybase also has compression, you can go from level 0 to level 9, the higher the level, the smaller the file will be but the longer it will take and it will also use more CPU in that case.
Here is a sample dump statement
dump database pubs2
to "/dev/db_dump_device"
with init
Log backups
Log backups are called incremental backups in Sybase, the command is dump tran, below are the 4 different variations
dump tran
will backup and removes the inactive portion
dump tran with truncate_only
removes the inactive part of the log without making a backup copy
dump tran with no log
removes the inactive part of the log without making a backup copy and without recording the procedure in the transaction log. Use no_log only when you are completely out of log space and cannot run the usual dump transaction command. Use no_log as a last resort and use it only once after dump transaction with truncate_only fails
dump tran with no_truncate
dumps a transaction log, even if the disk containing the data segments for a database is inaccessible, using a pointer to the transaction log in the master database. The with no_truncate option provides up-to-the-minute log recovery when the transaction log resides on an undamaged device, and the master database and user databases reside on different physical devices.
If you use dump tran with no_truncate you must follow it with dump database, not with another dump tran. If you load a dump generated using the no_truncate option, Adaptive Server prevents you from loading any subsequent dump.
Here is the complete syntax
dump tran[saction] database_name
to [compress::[compression_level::]]stripe_device
[at backup_server_name]
[density = density_value,
blocksize = number_bytes,
capacity = number_kilobytes,
dumpvolume = volume_name,
file = file_name]
[stripe on [compress::[compression_level::]]stripe_device
[at backup_server_name]
[density = density_value,
blocksize = number_bytes,
capacity = number_kilobytes,
dumpvolume = volume_name,
file = file_name]]
[[stripe on [compress::[compression_level::]]stripe_device
[at backup_server_name]
[density = density_value,
blocksize = number_bytes,
capacity = number_kilobytes,
dumpvolume = volume_name,
file = file_name] ]...]
[with {
density = density_value,
blocksize = number_bytes,
capacity = number_kilobytes,
compression = compress_level,
dumpvolume = volume_name,
file = file_name,
[dismount | nodismount],
[nounload | unload],
retaindays = number_days,
[noinit | init],
notify = {client | operator_console},
standby_access }]
Restores
Restores are named load in Sybase. Once you loaded a database it is in the offline state, you have to make it online
The load syntax is the same as the dump syntax. Instead of dump you use load and instead of to you use from
Example
load database pubs2
from "/dev/db_dump_device"
Advanced Backup Techniques
This module went into some things you have to do if you want to do a point in time restore or if you lost the latest backup what you can do to backup to the most recent log backup.
Sybase Central
The other day I had some complaints about Sybase Central, I still think it sucks compared to SQL Server Management Studio but some of the things I complained about have been addresses
When I said that I didn’t see results when running two commands, this is because by default Sybase Central only shows one result set. The icons are hideous, some of them have to be from NT4 or even windows 3.11
You will like this one…there is no shortcut or toolbar options to uncomment or comment a block of text….you have to go line by line or place /* before and */ after the text you want to comment. You can’t use multiple windows open in the same session, CTRL + N and it closes what you had open and you have a blank window, you have to open another instance of Interactive SQL to have multiple connections and sessions
For the rest, you will think that you are back using Query Analyzer
Monitoring the System
This started with the error log, where errors are stored and severity levels of errors, also discussed was how to prune the error log.
Next up was sp_who (what no sp_who2? Nope, doesn’t exist) and how to use KILL
Discussed next was how to monitor lock contention by using the sp_oject_stats stored procedure.
MDA Tables
These tables are similar to the sys.dm…… views in SQL Server. There are over 30 of these tables
sp_sysmon
This is a monitoring stored procedure, sp_sysmon displays information about Adaptive Server performance. It sets internal counters to 0, then waits for the specified interval while activity on the server causes the counters to be incremented. When the interval ends, sp_sysmon prints information from the values in the counters. See the Performance and Tuning Guide for more information.
Report section Parameter ---------------------------- ------------- Application Management appmgmt Data Cache Management dcache Disk I/O Management diskio ESP Management esp Index Management indexmgmt Kernel Utilization kernel Lock Management locks Memory Management memory Metadata Cache Management mdcache Monitor Access to Executing SQL monaccess Network I/O Management netio Parallel Query Management parallel Procedure Cache Management pcache Recovery Management recovery Task Management taskmgmt Transaction Management xactmgmt Transaction Profile xactsum Worker Process Management wpm
Here is a memory sample
sp_sysmon begin_sample
go
-- do something or wait
sp_sysmon end_sample, memory
go
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
