Get .sql file from SQL Server 2012 database
If you just want to generate a .sql script you can do this by right-clicking the database in Object Explorer and choosing Tasks > Generate Scripts:
Then you can select database and all objects, or you can select just the table you want:
Then select a location, and be sure to go into Advanced before continuing:
Under advanced, at the very least, change types of data to script to "Schema and Data" (if the reason you are doing this rather than a proper backup is because you need to import the data into a database earlier than SQL Server 2012, you should probably change the target server version option to whatever version you're targeting):
If you don't have that option in Management Studio, you are probably still running the RTM version of the client tools. Full functionality has finally been made free as of SP1. Download the most recent version - SP2 - here:
http://www.microsoft.com/en-us/download/details.aspx?id=43351
(You want one of the SQLManagementStudio
files.)
Importing SQL Server database from a .sql file
Create an empty database or if you have one already target that one.
Open CMD with elevated privilege and run:
sqlcmd -S SERVERNAME -d MYDATABASE -U USERNAME -P PASSWORD -i C:\path\mysqlfile.sql -o C:\path\results.txt
basically:
- -S: is your servername or localhost
- -d: is the database you are targeting
- -U: is the username
- -P: is the password
- -i: is the path to your .sql file
- -o: is where the logs file will be saved so if you had problem during the importation you can debug them
How do I import a sql data file into SQL Server?
If you are talking about an actual database (an mdf file) you would Attach
it
.sql
files are typically run using SQL Server Management Studio. They are basically saved SQL statements, so could be anything. You don't "import" them. More precisely, you "execute" them. Even though the script may indeed insert data.
Also, to expand on Jamie F's answer, don't run a SQL file against your database unless you know what it is doing. SQL scripts can be as dangerous as unchecked exe's
How do you import a large MS SQL .sql file?
From the command prompt, start up sqlcmd
:
sqlcmd -S <server> -i C:\<your file here>.sql
Just replace <server>
with the location of your SQL box and <your file here>
with the name of your script. Don't forget, if you're using a SQL instance the syntax is:
sqlcmd -S <server>\instance.
Here is the list of all arguments you can pass sqlcmd:
Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f | i:[,o:]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
Related Topics
A Simple Way to Sum a Result from Union in MySQL
How to Check All Stored Procedure Is Ok in SQL Server
How to Make Create or Replace View Work in SQL Server
Index for Multiple Columns in Activerecord
How to Get Second-Highest Salary Employees in a Table
What's the Right Way to Compare an Ntext Column with a Constant Value
Delete Top-N' Rows from a Table with Some Sorting(Order by 'Column')
How to Store Ordered Items Which Often Change Position in Db
Find All Columns of a Certain Type in All Tables in a SQL Server Database
How to Do SQL Select Top N ... in As400
Calculating Percentage Within a Group