December 9, 2002 at 6:10 am
Hi there,
I need to divide a 4GB SQL Server Database onto CD-ROMS.
Obviously they can only hold 600-650MB of data.
Would anyone know the best and quickest way to do this?
Thanks and regards,
Steve Harris
December 9, 2002 at 8:06 am
Are you looking to run your database off the CD-ROM? Or are you just trying to move your database from one server to another via CD-ROM media?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 9, 2002 at 8:56 am
CDs are notoriously slow to write data to. What are you trying to accomplish? backup? Moving the DB?
If you are just trying to get the data onto multiple files, there are many software packages that can take your large file (ie. your data base backup) and split it onto multiple distinct pieces of media.
Something this large is beyond anything I've personally ever done (or would want to for that matter 🙂
Be sure to delete as much invalid / old/ not needed data as you can before undertaking this.
December 9, 2002 at 10:06 am
I think you can use WinZip after you detach the file and span this across disks.
This is, I assume, for transferring this db to a client or somewhere else. If you want to run the db from a CD, this will not work without multiple CD drives. If you have them, then create filegroups and move the data onto separate file groups.
Steve Jones
December 9, 2002 at 8:08 pm
WinZip has an exact file size limit of 4gb. Might try RAR or ACE as I am not sure if they have the same limit or invets if SQLLiteSpeed or SQLZip as they do not have this limit. If you do compress usually the file is about 20% the original file size and may fit on one disk. If however you want to be able to access the data then I suggest making DBs of 600MB or 750MB (leave 50MB for File Allocation Table) depending on the media size and the size your burn software supports. Then make a copy of all the indexes and strutures and recreate in the new DBs.
Note : A quick way to do this is backup and restore the DB to another name and delete all the data then shirnk and set to desired size then backup and restore to individual database names.
Now once done copy portions of the data into each DB from the original and whenc completed truncate and shrink all the log files down so they are 1mb. Then you can quickly pull of the disk to get to.
Note I don't agree with reading DB from disk although I have heard of it done as HD is faster. Writing to disk is not as bad as used to be, you could write all in about 20minutes or less on the current 48x writers.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply