Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a SQL Server script file which ranges size of 3gb and I am in need to upload the script file to an external server.

i generated script for my 6gb database by following these steps

right-clicked the database-->tasks--->genrate scripts-->script entire database and objects and at scripting options i clicked advance -->type of data to script-->set it as schema and data and finished the data..

What are the shortest methods to upload faster into the server?

share|improve this question
You have a text (.sql?) file of 3 GB size? How did you generate it? What version of SQL Server is in question? – Marian Apr 5 at 12:00
@Marian .sql file size is 3gb and sql server version is ms sql server 2008 r2 32 bit – vignesh4303 Apr 5 at 12:05
2  
Why are you using a 32-bit version of SQL Server? Check your calendar; it's 2013. – Aaron Bertrand Apr 5 at 14:23
Could you please also list the size of the DB that had the 3GB script and how this 3GB script was created. Thanks. – StanleyJohns Apr 5 at 14:41
@StanleyJohns updated question please review it friend – vignesh4303 Apr 5 at 15:52

4 Answers

up vote 2 down vote accepted

If by "SQL Server script file" you mean a generated file with lots (and lots) of inserts to recreate your data then I would recommend a different approach.

I'd personally go with a database backup (why aren't you using that), but failing that I'd probably use a BCP extract. As a BCP exports the data in the format its stored you should get a smaller file. You can then compress this too with your choice of compression tool (e.g. zip).

Couple of notes on BCP:

  • You dont get a script with the table definitions - you'll still need to create these. You do get a format file which would help you reconstitute a table structure (to a point - e.g. no index info)
  • You will need to generate an extract per table - you dont get a single extract for all data in the database. Use a database backup for that.
  • BCP isn't the easiest tool in the world to use.
share|improve this answer
up vote 2 down vote

Personally, I'd do it using a compressed backup file.

Install an (Express will do) instance of SQL Server locally and run the script. Then, back it up and compress the backup file with a good utility (7-Zip/WinRAR compress very well and can create self-extracting archives). Transfer the compressed backup file (e.g. FTP), uncompress and restore the backup.

You can also compress just the 3GB textual script file and compare to see which is smaller to transfer.

share|improve this answer
2  
Note that Express will only work if the script does not implement any features that aren't supported in Express. – Aaron Bertrand Apr 5 at 14:22
As well since the backup is already compressed you are not going to get much more compression by zipping the file up. I just tried it on a compressed backup file with 7zip (default settings). Started with 6.56GB backup file, only compressed to 6.30GB. – Shawn Melton Apr 5 at 16:03
@Shawn well, if the edition is Express, they can't use backup compression unless they use a 3rd party tool. – Aaron Bertrand Apr 5 at 16:33
up vote 1 down vote

Without knowing the method this script was created, this questions is basically on how to transfer a 3GB text file from one computer to another (i.e. not related to DBs).

The answer to that would be what @RichardTheKiwi mentioned. Zip the file using maximum compression and then copy the file to the destination server and unzip.

share|improve this answer
update my question with process which i generated script – vignesh4303 Apr 5 at 15:53
Thanks @vignesh4303. It looks like what you need to do is a backup and restore. Kent Chenery has answered this. You can either use BCP or SSMS has an easy and intuitive visual interface for this. – StanleyJohns Apr 5 at 16:45
up vote 0 down vote

You have couple of options based on your suitablity :

a. Use BCP OUT and BCP IN as described here

b. Since you are using SQL2008R2, you can use backup compression. Also look for striping the backups as the generated files will be much smaller.

Note: The method that you are using makes very difficult to run the script. SSMS wont be able to handle a 3GB script and probably sqlcmd will barely do it.

share|improve this answer
2008 R2 only supports backup compression in Standard and Enterprise. We don't know what edition the OP is using. – Aaron Bertrand Apr 5 at 16:34
1  
Agreed and My bad for not mentioning it in my response.Thanks ! – Kin Apr 5 at 16:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.