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 :)

TSQL: Unique Constraint

To add a UNIQUE constraint to a column in a table:

ALTER TABLE {TableName} ADD CONSTRAINT {ConstraintName} UNIQUE({UniqueColumnName})

Such as:

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentName UNIQUE (NAME)

To add a UNIQUE constraint to two columns in a table:

ALTER TABLE {TableName} ADD CONSTRAINT {ConstraintName} UNIQUE
({UniqueColumnName1},{UniqueColumnName2})

Such as:

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentNameRoom
UNIQUE(NAME,RoomNumber)

Where is aspnet_regsql.exe

MSDN says

Aspnet_regsql.exe file is located in the
[drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server

But where exactly is that?

Here's an example of an absolute path of my aspnet_regsql.exe

C:\\Windows\Microsoft.NET\Framework\V2.0\aspnet_regsql.exe

Based on that example, it may be easier for you to determine where exactly your server's aspnet_regsql.exe file is. Be sure to take note of your server's OS (Framework vs Framework64) and its version.