Get .sql file from SQL Server 2012 database

问题:

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

回答1:

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

回答2:

You have all these alternatives to start the SQL Server Import and Export Wizard

• On the Start menu, point to All Programs, point to Microsoft SQL Server, and then click Import and Export Data.
• In SQL Server Data Tools (SSDT), right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.
• In SQL Server Data Tools (SSDT), on the Project menu, click SSIS Import and Export Wizard.
• In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
• In a command prompt window, run DTSWizard.exe, located in C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ (or probably \110\ rather than \100\ in your case).

Saludos ;)

回答3:

Right click on the database under Tasks click generate scripts and wizard will open and then you can export the table structure and data to another database.

You have to select the tables and other schema you want to export and on one of the pages is a check box to select export table data.

This will generate sql statements for your database.

This may not be available in the express version, I've not checked.