A lot of the time we deal with small databases with small data. Importing a backup database is usually ok for these databases using the SQL Server Management Studio. We would normally just open the .sql file we want to import in Management Studio and then click on the Execute button to import it into a new or existing database. Sometimes this just isn't possible. Large databases or big tables mean that the overhead required for SQL to parse and run the scripts just isn't possible.

Normally under these scenarios SQL Server Management studio will inform you that it can't do this by telling you:

Cannot execute script.

Exception of type ‘System.OutofMemoryException’ was thrown.

Don't panic! The solution is to use the built in sql command line tool instead of using the visual SQL Server Management Studio application.

First up, make sure the database you want to import to has been created already.

Next, fire up the command line: Start -> Run -> Type cmd and press enter

Once the command line is on screen tell SQLCMD to import your database by doing something like this:

Sqlcmd -S [server instance name] -d [database name] -i [filename you want to import]

If you're not sure about the server instance name simply open up SQL Server Management Studio and on the login screen take a note of the Server Name textbox as this is the value you need to use as the server instance name.

As an example you should have a line that looks something like this:

sqlcmd -S MyServer\SQLServerInstance -d MyDatabase -i C:\sqlToImport.sql

There are a whole bunch of options you can use while importing, like specifying the username and password to use for a database, which you can read all about over on MSDN - http://msdn.microsoft.com/en-us/library/ms165702%28v=sql.105%29