If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > Demove device from database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2002
Location: BC, Canada
Posts: 17
Demove device from database

Hi guys,

I inherited a server with one database that is oversize, having less than 10% of data in it. It has several devices so I would like to move data to the single device, to remove unused devices and drop them.

Question is how to remove unused device from the user database.

Thank you.
__________________
Marin Kostadinovic
----------------------------
DBA
DIMM Info Systems Inc.

Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2012
Location: Lisbon
Posts: 98
Hello,

So to drop devices, you have to first drop the DB.

So before do anything, i advise you to make a dump of the tb, and save the current devices setting present for that db - sp_helpdb "name"

So if you have to rollback your action, everything can be like before.

Now you can't use dump and load, because to load the db size has to be the same or higher.

So you options are:
1- Bcp out the tables.
You bulk copy out the tables then "drop database" , "sp_dropdevices" and drop the devices that you want to drop. And after recreate the db with the size you want, and bcp in the tables.

2- Create a new db on the server, with the size you desire, copy table data from one db to another, drop the old one, and rename (sp_renamedb) the new to the old name.



Hope it helps

Last edited by Catarrunas; 11-01-12 at 05:46.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2002
Location: BC, Canada
Posts: 17
That is what I was afraid of.

Bulk copy out and in is not a problem (with 200+ tables though it is not that trivial). On top of it, all indexes and keys got to be recreated after bcp in.

Thank you guys.
__________________
Marin Kostadinovic
----------------------------
DBA
DIMM Info Systems Inc.

Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 95
As dump and load is not an option at all, also I don't think there is any way to reduce the size of a database.
So the BCP seems only solution in this case.
To take care of indexes and other key constraints u need to use sybase re-engineering using power builder to genrate ddl of all the objects of the database, so the steps should be for the second option in 2nd post (which seems to be more safer)

1. Take bcp out of all the tables.
2. Genrate DDL for all the objects(table,indexes,triggers,sp etc) with the help of powerbuilder.
3. Create a new database of desired size.
4. Create the tables.
5. BCP in the data to the tables.
6 create rest of the objects using DDL that was genrated in 2nd step.
7. check if everything is fine, drop the old database and rename the new database with the name of old database.

to take the bcp of number of tables, below may help

1. execute below from sybase prompt
select 'bcp my_db'+'..',name +'out',name+'.bcp.out -UUser_name -SASE_Server_Name -Ppassword -c' from sysobjects where type='U'

2. Above will genrate the bcp command for all the user tables in the database.

3. execute them from OS prompt, to get the bcp out.


Hope this may be helpful
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On