Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-08 : 07:53:48
|
| Vijender Kumar Saini writes "Hi!I want to backup my data that is in SQL Server 2000, in the form of scripts( like multiple insert statements.)I have to transfer data (of approx 800 mb) from a system to another, and normal backing up will take a lot of space.Please Help Vijender :-(" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-08 : 07:53:41
|
| Generating the data as INSERT statements will take up A GREAT DEAL MORE space than a normal SQL Server backup file will. Backing up and restoring is the smallest and most efficient method. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-08 : 12:21:02
|
| Well, here is a stored procedure that would let you do this ([url]http://vyaskn.tripod.com/code.htm#inserts[/url], but as Rob mentioned it is going to take up more space than a backup. I use the stored procedure when I want to refresh lookup tables in different environments. I also use bcp for this.You could use bcp or DTS to do the transfer and just do one table at a time to avoid space limits. This would be recommended over the INSERT way.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-08 : 12:52:25
|
| You would be better off using bcp to extract data.If you are concerned about the size of the file to transfer then do a backup and zip the backup file. It usually zips very well.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-08 : 12:54:59
|
quote:
Originally posted by nr
It usually zips very well.
Yeah, I've seen it zip between 15:1 and 10:1. Databases (not just SQL Server) zip up pretty good. So on a 800MB database, you can expect the file size of the zip file to be around 80MB.Tara |
|
|
|
|
|
|
Backing up data in Scripts.