How to move DB from one server to a new server

Janet Xu

The information shown below are only for Sybase:

1> dump database emas_dev to '/home/emasld/backupfile'
2> go
Backup Server session id is:  135.  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 /home/emasld/backupfile.
Backup Server: 6.28.1.1: Dumpfile name 'mas_dev081190A275' section number 0001
mounted on disk file '/home/emasld/backupfile'
Backup Server: 4.58.1.1: Database emas_dev: 490 kilobytes DUMPed.
Backup Server: 4.58.1.1: Database emas_dev: 832 kilobytes DUMPed.
Backup Server: 4.58.1.1: Database emas_dev: 868 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 emas_dev: 876 kilobytes DUMPed.
Backup Server: 3.42.1.1: DUMP is complete (database emas_dev).
1> dump database emas_prod  to '/home/emasld/backupfileemas_prod`
1>  dump database emas_prod  to '/home/emasld/backupfileEMASPROD'
2> GO
Backup Server session id is:  139.  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
/home/emasld/backupfileEMASPROD.
Backup Server: 6.28.1.1: Dumpfile name 'as_prod081190A305' section number 0001
mounted on disk file '/home/emasld/backupfileEMASPROD'
Backup Server: 4.58.1.1: Database emas_prod: 810 kilobytes DUMPed.
Backup Server: 4.58.1.1: Database emas_prod: 27676 kilobytes DUMPed.
Backup Server: 4.58.1.1: Database emas_prod: 27846 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 emas_prod: 28050 kilobytes DUMPed.
Backup Server: 3.42.1.1: DUMP is complete (database emas_prod).
1> dump database emas_STAGE
1> dump database emas_stage '/home/emasld/backupfileEMASSTAGE'
2> go
Msg 102, Level 15, State 1:
Line 1:
Incorrect syntax near '/home/emasld/backupfileEMASSTAGE'.
1> dump database emas_stage to '/home/emasld/backupfileEMASSTAGE'
2> go
Backup Server session id is:  143.  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
/home/emasld/backupfileEMASSTAGE.
Backup Server: 6.28.1.1: Dumpfile name 's_stage081190A38B' section number 0001
mounted on disk file '/home/emasld/backupfileEMASSTAGE'
Backup Server: 4.58.1.1: Database emas_stage: 438 kilobytes DUMPed.
Backup Server: 4.58.1.1: Database emas_stage: 590 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: 4.58.1.1: Database emas_stage: 594 kilobytes DUMPed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.58.1.1: Database emas_stage: 598 kilobytes DUMPed.
Backup Server: 3.42.1.1: DUMP is complete (database emas_stage).

ftp the backup files to a new server than load the DB to the new server.

1> create database emas_dev
2> for load
3> go
CREATE DATABASE: allocating 1024 pages on disk 'master'

1> alter database emas_dev
2> on default=25
3> for load
4> go
Extending database by 12800 pages on disk master
1> load database emas_dev from "/bin/backupfile"
2> go
Backup Server session id is:  10.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'mas_dev081190A275' section number 0001
mounted on disk file '/bin/backupfile'
Backup Server: 4.58.1.1: Database emas_dev: 1448 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_dev: 19974 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_dev: 25608 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_dev: 25616 kilobytes LOADed.
Backup Server: 3.42.1.1: LOAD is complete (database emas_dev).
Caution:  You have set up this database to include space on disk 0 for both data
and the transaction log.  This can make recovery impossible if that disk fails.
All dumped pages have been loaded.  SQL Server is now clearing pages above page
12800, which were not present in the database just loaded.
SQL Server has finished clearing database pages.
Use the ONLINE DATABASE command to bring this database online; SQL Server will
not bring it online automatically.
ap1459(11:38:06): not optimal to have the data and log segment on the same disk, but that is how we are set up on the old server too.
Janet(11:38:37): Oh ok
ap1459(11:38:37): as long as you are taking regular backups you should be fine.
1> online database emas_dev
2> go
Database 'emas_dev' is now online.
1> disk init name="dumpprod",
2> physname = "/dev/dumpprod",
3> vdevno=6,
4> size=64000
5> go
1> create database emas_prod
2> on dumpprod=125
3> for load
4> go
CREATE DATABASE: allocating 64000 pages on disk 'dumpprod'
1> load database emas_prod from "/bin/backupfileEMASPROD"
2> go
Backup Server session id is:  16.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'as_prod081190A305' section number 0001
mounted on disk file '/bin/backupfileEMASPROD'
Backup Server: 4.58.1.1: Database emas_prod: 1764 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_prod: 39944 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_prod: 83464 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_prod: 126476 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_prod: 128012 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_prod: 128022 kilobytes LOADed.
Backup Server: 3.42.1.1: LOAD is complete (database emas_prod).
Caution:  You have set up this database to include space on disk 6 for both data
and the transaction log.  This can make recovery impossible if that disk fails.
Redo pass of recovery has processed 1 committed and 0 aborted transactions.
Use the ONLINE DATABASE command to bring this database online; SQL Server will
not bring it online automatically.
1> online database emas_prod
2> go
Database 'emas_prod' is now online.
1> disk init name="dumpstage",
2> physname = "/dev/dumpstage",
3> vdevno=2,
4> size=12800
5> go
1> create database emas_stage
2> on dumpstage=25
3> for load
4> go
CREATE DATABASE: allocating 12800 pages on disk 'dumpstage'
1> load database emas_stage from "/bin/backupfileEMASSTAGE"
2> go
Backup Server session id is:  19.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 's_stage081190A38B' section number 0001
mounted on disk file '/bin/backupfileEMASSTAGE'
Backup Server: 4.58.1.1: Database emas_stage: 1416 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_stage: 25606 kilobytes LOADed.
Backup Server: 4.58.1.1: Database emas_stage: 25614 kilobytes LOADed.
Backup Server: 3.42.1.1: LOAD is complete (database emas_stage).
Caution:  You have set up this database to include space on disk 2 for both data
and the transaction log.  This can make recovery impossible if that disk fails.
Use the ONLINE DATABASE command to bring this database online; SQL Server will
not bring it online automatically.
1> online database emas_stage
2> go
Database 'emas_stage' is now online.


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/67335/showart_684814.html