CODEWITHSUNDEEP
sqlsql-server-2008
G dangi
G dangi

Reputation: 188

How to execute generated script(.sql file) with schema and data in SQL Server 2008

Using SSMS 2008 I am able to generate a script for a database with huge amounts of data in file ABC.sql on my desktop.

The database has approx. 9 GB of data so I'm unable to open the file. Is there any way to execute the script?

When I try to open it in SSMS I get an error:

The operation could not be completed. not enough storage is available to complete this operation

The template specified cannot be found. Please check that the full path is correct

Upvotes: 2

Views: 10372

Answers (3)

user5093161
user5093161

Reputation:

You can generate script of your database by RightClick on your database Tasks>GenerateScripts> click next on Generate and Script window Check on select specific table choose tables you want Press next Click on Advance option on end of General Category select Type of data to script now choose which kind you want your database to. Scheme Only: Means this script will create your database. DataOnly:If you have created database and table this will insert data into it. Press ok then Next. Your file is by default save in C:\Users[UserName]\Documents\ .

Upvotes: -1

Milena Petrovic
Milena Petrovic

Reputation: 2771

SQL Server offers 2 command prompt features that can se used for executing large queries - osql (will be removed in future), and sqlcmd

osql is located in the Tools\Binn subfolder. To execute a SQL script:

  1. Start the Command Prompt
  2. Navigate to the folder where the osql utility is located

  3. Run the command in the following format:

    osql –H <workstation name> -S <server_name[\instance_name]> -U <user login ID> -P <login password> –i <full path to script>

To execute the large.sql file located in the D:\test, against the Central database on the SQL Server instance Dell\SQL2012, as an sa with the 'sqladmin' password, run the following command:

osql -H Dell -S Dell\SQL2012 -i D:\test\large.sql -U sa -P sqladmin

The sqlcmd command line utility is also located in the SQL Server’s Tools\Binn sub-directory. To execute a SQL script:

  1. Start the Command Prompt
  2. Navigate to the folder where the sqlcmd utility is located

  3. Run a command in the following format:

    sqlcmd –S <server name> -d <database name> -i <full path to script> -U <user login ID> –P <login password>

To execute the same as above, run the following command: 

sqlcmd -S Dell\SQL2012 -d Central -i D:\test\large.sql -U sa –P sqladmin

Start the sqlcmd Utility Run Transact-SQL Script Files Using sqlcmd

Upvotes: 4

devio
devio

Reputation: 37205

I use sqlcmd to execute large SQL files.

Upvotes: 1

Related Questions