Data Migration - Insert Script / Fresh install

Question

Data Migration - Insert Script / Fresh install

Imtiaz

Old Hand

Points: 331
More actions
September 6, 2005 at 5:35 pm

#109997

Hi

I have some tables in my SQL Server 2005 database. I need to create insert scripts for them. Since these tables contain the CLOB and BLOB values and the number of records is huge, I need to devise a way to install this table data on any new database(Assuming the new database is created and the table structure exists) .Can some one suggest me how I can do this ?

DTS or SSIS is out of question because least manual interference is required.

Thanks & Regards
Imtiaz

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

the best answer is to create a backup of the database, and restore the database, instead of trying to script it out.

I don't think you can script out a TEXT, or IMAGE (BLOB and CLOB equivilents), so you should go with teh safest way...restore a backup.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Imtiaz Old Hand Points: 331 More actions · Answer 2

Is there anything similiar to dump file of Oracle?

Lowell SSC Guru Points: 323546 More actions · Answer 3

yes; a SQL Server backup is the equivilent of a dump file; the differences are it restores much faster than a dump, and it is really binary in nature; opening it up in a text editor doesn't allow you to pul out SQL statements like in oracle 8 dumps. (oracle 10 dumps are binary now too).

You would do a backup of a "perfect" database and give that to a client; otherwise you could script out all the objects, as well as script out the inserts for non-TEXT/IMAGE/VARBINARY/NVARBINARY as sql statements with this script:

http://vyaskn.tripod.com/code.htm#inserts

but because you need binary data inserted into your db, you need to use a backup, which would be restored onsite elsewhere (at the client?)

The backup has everything in it...schema, data regardless of datatype, constraints etc without the overhead of tablespaces and users. (ie if the tablespace ro user doesn't exist, you can't restore)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Imtiaz Old Hand Points: 331 More actions · Answer 4

Hi Lowell

Thanks for the valuable answers and being patient to reply back promptly...

I have used Vyas's site to get the insert scripts for non-text data. For others I will maintain a separate database in my dev environment which will just contain the table data for text,image...Since this information is more or less static this database would not undergo any changes. A backup of this db would then be taken.

While deploying at the CLient location, the command script for installing operational data would call the SQL insert scripts and create a new database (from the backup we have). Then we have insert the data into the client db from this newly created database. Once that is done, the db would be dropped.

Do you see any flaw in the above approach. We cannot create client database from backups as that is not the accepted norm for our product...

Lowell SSC Guru Points: 323546 More actions · Answer 5

Wherever possible, the backup should have all the data required; ie all lookup tables for static drop down lists such as "status" and "state" and whatnot are in the backup.

I assume that some setup scripts have to be customized for each client? ie inserting specific default values into tables for "agency name" and whatever else is client specific;

while you could certainly script out that portion of the process and provide the script to the client along with the backup, i believe I would even do that piece in the shop, where i would take the db, run the scripts on it, and then back it up and send it to the client for restoration;

after rereading your post it sounds like you would run a program which would wrap those steps up together, which is exactly what i would do.

That's personal preference, because I've had too many customers who were not familiar with SQL run a script on the default "master" database instead of the correct client database...or require handholding over the phone just to be sure theirs a confident voice onhand when the execute a script...I'm trying to eliminate areas where an inexperienced user might muck things up.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Imtiaz Old Hand Points: 331 More actions · Answer 6

Imtiaz

Old Hand

Points: 331

September 7, 2005 at 12:58 pm

#588225

Appreciate your thoughts on the matter