Sybase跨平台dump/load 操作说明

ASE 12.5.3以上的版本可以进行跨平台dump/load 。



一．执行的 dbcc checkdb或任何其他dbcc命令来校验数据库运行干净
$ isql -Usa -P -SASE125
1> dbcc checkdb("tec")
2> go
Checking tec: Logical pagesize is 2048 bytes
Checking sysobjects: Logical pagesize is 2048 bytes
The total number of data pages in this table is 32.
Table has 336 data rows.
……………………………………………………….
……………………………………………………….
Checking tgh_jx: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 25 data rows.
Checking gy_con_area: Logical pagesize is 2048 bytes
The total number of data pages in this table is 1.
Table has 17 data rows.
Checking gy_user_work_card_s1: Logical pagesize is 2048 bytes
The total number of data pages in this table is 3.
Table has 106 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

二．使用sp_dboption设置数据为单用户模式
1> use master
2> go
1> sp_dboption tec,"single user",true
2> go
Database option 'single user' turned ON for database 'tec'.
Running CHECKPOINT on database 'tec' for option 'single user' to take effect.
(return status = 0)

三．使用sp_flushstats将内存中存储的统计信息刷新到systabstats系统表。你必须至少等 待10秒钟，等待进程完成
1> use tec
2> go
1> sp_flushstats
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

四．运行checkpoint命令 将所有脏页（自上次写入以来被更新的页）写入到数据库设备。你必须至少等待10秒钟，等待进程完成。
1> checkpoint
2> go

五．运行卸载数据库。
1> use master
2> go
1> dump database tec to "/data_backup/tec0912zm"
2> go
Backup Server session id is:  4.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /data_backup/tec0912zm.
Backup Server: 6.28.1.1: Dumpfile name 'tec0825608D59    ' section number 1
mounted on disk file '/data_backup/tec0912zm'
Backup Server: 4.58.1.1: Data base tec: 14644 kilobytes DUMPED.
Backup Server: 4.58.1.1: Database tec: 36278 kilobytes DUMPED.
Backup Server: 4.58.1.1: Database tec: 57912 kilobytes DUMPED.
…………………………………………………………………………….
Backup Server: 4.58.1.1: Database tec: 585206 kilobytes DUMPED.
Backup Server: 4.58.1.1: Database tec: 586744 kilobytes DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.58.1.1: Database tec: 586752 kilobytes DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database tec).

六．取消sp_dboption单用户模式设置
1> sp_dboption tec,"single user",false
2> go
Database option 'single user' turned OFF for database 'tec'.
Running CHECKPOINT on database 'tec' for option 'single user' to take effect.
(return status = 0)

（必须在master 库  更改database options
You must be in the 'master' database in order to change database options.）

七．将dump文件拷贝到windows -ASE 服务器中
1.可使用ftp拷贝
2.也可以使用ftp工具 拷 贝


八．将windows-ASE服务器中建设备、建数据库
disk init  name  = 'webquery_data',
physname  = 'c:/sybase/data/webquery_data.dat',
size  = '300M',
go

disk init  name  = 'webquery_log',
physname  = 'c:/sybase/data/webquery_log.dat',
size  = '90M',
go

九．建数据库
CREATE DATABASE cpy_webquery
            ON webquery_data = '300M'
        LOG ON webquery_data = '90M'
go

十．Load数据库
load database cpy_webquery from "d:/data_backup/webquery090317.dat"

十一.数据库联机
online database cpy_webqeury

十二.重编译索引
sp_post_xpload  


以下 为官方文档 原 文：
Dump and Load a Database Across Platforms
Adaptive Server Enterprise supports both the big endian and little endian platforms.
Overview
Adaptive Server Enterprise, version 12.5.2, allowed the dump and load of databases across platforms with the same endianness architecture.
With Adaptive Server Enterprise, version 12.5.3, the dump and load databases across platforms can now be done with different endianness architecture. This means that a dump database and load database can be done from a big endian platform to a little endian platform and from a little endian platform to a big endian platform.
A big endian platform is where the most significant byte is with the lowest address. The little endian platform is where, within a given 16 or 32 bit word, bytes at the lower addresses have a lower significance.
There is no syntax change with dump or load database in version 12.5.3. Adaptive Server automatically detects the endian of the database dump file at the time of a load database, then performs the necessary conversions. Loads in an older version, such as 11.9 and 12.0, are also supported. The dump and load can be from 32 bit to 64 bit platforms, and vice versa.
Endian platforms
Platforms supported:

Big endian        Solaris 32/64        IBM 32/64        SGI 32/64        HPPA 64        HPIA 64        MAC 32

Little endian        Linux 1A 32        Linux 1A 64        NT        Sun X86
Dump and load across platforms with the same endian architecture
When dump database and load database are done across platforms with the same endian architecture, user and system data do not require conversions. There are no limitations on operations with the dump and load of a database. Adaptive Server Enterprise supports dump and load processes for transactions and databases across platforms.
Dump and load across platforms with different endian architecture
Dumping a database
Before you run dump database, use the following procedures to move the database to a transactional quiescent status:
1Verify the database runs cleanly by executing dbcc checkdb or any other dbcc command.
2To prevent concurrent updates from open transactions by other processes during the dump database, place the database in a single user mode with sp_dboption.
3Flush statistics to systabstats with sp_flushstats. You must wait for at least ten seconds for the process to complete.
4Run checkpoint against the database to flush updated pages. You must wait for at least ten seconds for the process to complete.
5Run dump database.
Loading a database
Once you load the database, Adaptive Server automatically identifies the endian type on the dump file and performs all necessary conversions during the load database and online database.
Note When Adaptive Server converts the order for the index rows some may be incorrect, you must recreate the indexes after loading the database. See “sp_post_xpload” on page 27 for rebuilding indexes.
Restrictions
•Remote dump transaction and load transaction to or from a back up server are not supported.
•A password protected dump file cannot be loaded across platforms.
•dump transaction and load transaction is not allowed across platforms.
•If you dump database and load database for a parsed XML object, you must parse the text again after the load database is completed.
•You cannot perform the dump database and load database across platforms on Adaptive Servers version earlier than 11.9.
•Embedded data structures stored as binary, varbinary, or image columns are not done because Adaptive Server cannot translate these structures.
•When you dump and load a master database, you must recreate all logins in the syslogins because passwords are incompatible between platforms.
•Reset the password using the command line argument -psa on a master database after the load database is completed.