Get .sql file from SQL Server 2012 database

sql sql-server sql-server-2012-express
Andrew picture Andrew · Apr 1, 2013 · Viewed 61.2k times · Source

I need to get .sql files from an existing database with SQL Server 2012. Is this possible because all I see in the export option is delimited files.

Ex.) I have table1 with 30 records. I need a file that would be something like what is below. (I know my syntax is incorrect, but you should get the point).

CREATE TABLE table1 (
    INSERT INTO table1 values (
   .......)

Answer

Aaron Bertrand picture Aaron Bertrand · Apr 1, 2013

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:

enter image description here

Then you can select database and all objects, or you can select just the table you want:

enter image description here

Then select a location, and be sure to go into Advanced before continuing:

enter image description here

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

enter image description here

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

Related questions

How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31')

I have a connection to a MS SQL Server 2012 database in classic ASP (VBScript). This is my connection string: Provider=SQL Server Native Client 11.0;Server=localhost; Database=databank;Uid=myuser;Pwd=mypassword; When I execute this SQL command: UPDATE [info] …

Read More
Can't create stored procedure with table output parameter

I have this code: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProfitDeals]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[GetProfitDealsVar2] GO IF EXISTS(SELECT 1 FROM sys.types WHERE name = 'TableOrderType' AND is_…

Read More
Hiding databases for a login on Microsoft Sql Server 2008R2 and above

Please can anyone assist with hiding the available databases on sql server 2008R2 or newer versions. I have a new login user that I mapped to a specific database. When logging in with the specific login user I can see …

Read More