[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

ASE 11.5 from NT4 Server to W2K Server (Moving Database)

What is the best way to move the entire database from one installation to another. I've seen many, many options and I have yet to have to much success with any. The object here is an OS upgrade, not Sybase so I guess what I'm looking to do is duplicate what is on the old NT4 server to the W2k server. Unfortunately a Sybase Upgrade as well is out of the question.

I was under the impression I would be able to create the new device, database and somehow do a dump of the data and all to the new database. I though it would be more efficient dataspace wise. Unfortunately I am not all that familiar with the DBA tasks in Sybase. To tell you the truth I'm not even to sure that the way the dev and database was created is as efficient as it could be.
As far as exporting, BCP seems a little clunky. I would have to dump each and every item indiviually including all stored procs, but it's not out of the question. I guess I could write somekind of sp to do it...

Is there a best way ? If not, some opinions would be helpfull hurt or even some pointers from someone who's been here before.
0
caligor
Asked:
caligor
1 Solution
 
Jan FranekCommented:
I usually do it this way:
1. install Sybase ASE on destination machine - the same version as on source machine - preferably into the same directory
2. create ASE server with the same name as on source machine
3. stop ASE on both destination and source machine
4. copy all database device files into the same locations on destination machine
5. copy SQL.INI and SERVERNAME.cfg configuration files
6. change IP address of source machine to something unused and destination machine to original IP adress of source machine
7. say a prayer and start ASE on destination machine :-)

If you did everything correctly, it should run without problem - exactly the same server. This is quickest way I know of.
0
 
Jan FranekCommented:
Other possible way - using binary dumps:
steps 1. and 2. are the same
3. create database devices - at least the same size as on source server - they don't have to be on the same locations
4. create all user databases - warning - they HAVE to be created the same way as on source server - the same sizes of data and log segments and the same sequence of devices
5. lock all logins (except one that you will use) - or use another way to ensure, that your source data will not change
6. dump databases on source server (using DUMP DATABASE command)
7. load databases on destination server (using LOAD DATABASE command)
8. stop both servers
9. copy configuration files and change IP adresses
10. prayer and start :-)
0
 
Jan FranekCommented:
Third possible way - if you want to "clean up" your server - create new clean databases, copy table structures, move data using bcp, copy other objects (indexes, views, procedures etc). I don't recommend it if you are not experienced Sybase DBA.
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
caligorAuthor Commented:
I tried using Dump, but the dump does not allow me to specify a path on NT. I I specify a path it fails, if no path is specified it writes out to the system32, and there is not enough space there. Is there way to specify the location of the dumpfile on NT ?
Thank you for your help.
0
 
bretCommented:
You should post the exact command you attempted and the failure message.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Not sure what you mean by "dump does not allow me to specify a path" - DUMP DATABASE requires a path and filename...

... unless you are using a "dump device" which is a shortcut for a predefined path and filename.
0
 
caligorAuthor Commented:
Here is an example of with the path and without the path.
I see the createfile failed, but there is no good reason it fails. I've checked the permissions and it is not an issue as far as I can tell. Full rw permissions are granted to all for any path I have attempted. However if I do not specify path onliy filename I get the latter...

This is the command issued :
dump database HSI_US to "q:\dumpfil"

Here is the response :

Server Message:  Number  3216, Severity  10
Line 1:
Backup Server session id is:  40.  Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Server Message:  Number  404101, Severity  1
Server 'TUVIX_BS', Procedure 'bs_write_header', Line 0:
Backup Server: 4.41.1.1: Creating new disk file q:\dumpfil.
Server Message:  Number  414102, Severity  2
Server 'TUVIX_BS', Procedure 'bs_write_header', Line 0:
Backup Server: 4.141.2.40: [7] The 'CreateFile' call failed for device 'q:\dumpfil' with error number 5 (Access is denied). Refer to your operating system documentation for further details.
Server Message:  Number  8009, Severity  16
Line 1:
Error encountered by Backup Server.  Please refer to Backup Server messages for details.

Without path :
command issued :
dump database HSI_US to dumpfil

Response:
Server Message:  Number  3216, Severity  10
Line 1:
Backup Server session id is:  37.  Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Server Message:  Number  404101, Severity  1
Server 'TUVIX_BS', Procedure 'bs_write_header', Line 0:
Backup Server: 4.41.1.1: Creating new disk file C:\WINNT\system32\dumpfil.
Server Message:  Number  602801, Severity  1
Server 'TUVIX_BS', Procedure 'bs_write_header', Line 0:
Backup Server: 6.28.1.1: Dumpfile name 'HSI_US0510308C20 ' section number 0001 mounted on disk file 'C:\WINNT\system32\dumpfil'
Server Message:  Number  405801, Severity  1
Server 'TUVIX_BS', Procedure 'bs_begin_phase', Line 0:
Backup Server: 4.58.1.1: Database HSI_US: 12002 kilobytes DUMPed.
Server Message:  Number  405801, Severity  1
Server 'TUVIX_BS', Procedure 'bs_begin_phase', Line 0:
Backup Server: 4.58.1.1: Database HSI_US: 31090 kilobytes DUMPed.
Server Message:  Number  405801, Severity  1
Server 'TUVIX_BS', Procedure 'bs_begin_phase', Line 0:
Backup Server: 4.58.1.1: Database HSI_US: 59492 kilobytes DUMPed.
0
 
Jan FranekCommented:
...failed for device 'q:\dumpfil' with error number 5 (Access is denied)... - it means, you probably don't have rights to write into q:\ folder. By "you" I mean account, that ASE runs under
0
 
caligorAuthor Commented:
Although I checked that the group everone had access... I lacked in checking if the user sybase was running under was in group everyone. Thanks for making me reCheck myself. Although Another problem has arisen now on the load. I'll post another.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now