Run SQL Script from command line

I have a 1GIG update script generated by my SQL Server Management Studio, how would you run it?

Original story: I have a perfectly running database in 2008 that has to be transferred to a 2005 server. I tried back-up and restore from 2008 to 2005 servers but that got all weird so I needed a new strategy.

Solution: Generate Script.

So I generated a script (destination: file instead of a new query window because openning a file and writing a 1Gig worth of text on it would result to a not-enough memory error). As a result, I have a 1GB update script that I can't open on the Management Studio but I have to run on 2005.

Solution: call it from the command prompt then run it.

Here's how:

1. Make sure that your script has USE Database statement at the very top. You don't have to open your file to check (actually you SHOULDN'T or your machine might hang). Just make sure you toggle the option

2. On command line type: OSQL -U [UserLogin] -i [absolutePathOfSQLscript]

For example:

OSQL -U sa -i C:\Users\Oyen\MyDocuments\1Gig_MergeScript.sql

**Note: Case matters (U != u)

You may also opt to display the script ran with the token -e which would look like: OSQL -U [UserLogin] -e -i [absolutePathOfSQLscript]

3. Password will be prompted if it is required by the login, then simply provide the password **Notice that whatever you type will not be displayed for security.

4. Press enter then wait until the script is finished running.

To note: it took me less than an hour to run a 1gigabyte update script. I hope that would help you estimate how long it will take you to run yours :)

No comments: