CODEWITHSUNDEEP
sqlsql-serverdatabasebackup
IAdapter
IAdapter

Reputation: 64707

How to backup Sql Server to sql file?

In "Back UP" I only get a bak file, but I would like to create .sql file

Upvotes: 29

Views: 33552

Answers (7)

Travis Heeter
Travis Heeter

Reputation: 14044

Ok, I read through most of these, but I had no "advanced button". But, there is still a way to do it, it's just a little hard to find, so here you go:

enter image description here

Upvotes: 5

p.campbell
p.campbell

Reputation: 100557

Use SQL Server's Generate Scripts commend

  • right click on the database; Tasks -> Generate Scripts
  • select your tables, click Next
  • click the Advanced button
  • find Types of data to script - choose Schema and Data.
  • you can then choose to save to file, or put in new query window.
  • results in CREATE and INSERT statements for all table data selected in bullet 2.

SQL Server Generate Scripts Data

Upvotes: 55

Simon
Simon

Reputation: 9425

This is a possible duplicate of: SQL script to get table content as "SELECT * FROM tblname"

To do a full database backup to File/Query you can use the 'Generate Scripts...' option on the Database.

Open SQL Server Management studio, right click on the database and choose 'Tasks->Generate Scripts...'

Then use the wizard to backup the database. You can script the whole database or parts of it. Two important options: In the 'Advanced' section, you will probably want to ensure 'Type of backup = 'Schema and Data' and the 'Script Statistics' is on.

This will produce a *.sql file that you can use as a backup that includes the schema and table data.

Upvotes: 5

Pleun
Pleun

Reputation: 8920

If you want a file with insert statements for your data have a look here:

This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.

http://vyaskn.tripod.com/code.htm#inserts

Upvotes: -1

DOK
DOK

Reputation: 32831

This fellow may have achieved what you are trying to do by creating the backup, and then restoring it and giving it a new name.

This approach copies the data along with all of the database objects.

Upvotes: 0

squillman
squillman

Reputation: 13641

If you want to create a script of your database you right-click on the databases and Generate Scripts (it's in different sub-menus depending on what version of SQL and Enterprise Manager / SQL Server Management studio you're using).

That will, however, only get you the database objects. It will not generate scripts for data. Backing up a database will give you all of the database objects as well as the data, depending on what recovery model your database is set to.

Upvotes: 0

cusimar9
cusimar9

Reputation: 5259

You can generate a script from a database, see http://msdn.microsoft.com/en-us/library/ms178078.aspx

Upvotes: 2

Related Questions