CODEWITHSUNDEEP
sql-serverssms
Alex Gordon
Alex Gordon

Reputation: 60691

SQL Server: how do I export entire database?

I need to export database from one server and import it into another server.

  1. How do I export the entire database to a file, or two files mdf, ldf (either option is fine)
  2. How do I import it into a new server using ssms?

In the instructinos frmo ponies, it says:

In the To a point in time text box, either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box. For more information, see How to: Restore to a Point in Time (SQL Server Management Studio).

To specify the source and location of the backup sets to restore, click one of the following options:

From database

Enter a database name in the list box.

I am unable to type anything in restore

https://stackoverflow.com/questions/3241108/i-backed-up-the-database-in-ssms-sql-server-how-do-i-restore-it

Upvotes: 19

Views: 61672

Answers (5)

chainstair
chainstair

Reputation: 817

If you want to export / save all data of a database into a .sql file, do this:

  1. Right click on the database in SQL Server Management Studio
  2. Tasks -> Generate Scripts
  3. Script entire database and all database objects
  4. Next
  5. Click - Advanced
  6. Scroll down to Types of data to script and set from Schema only -> Schema and data -> Ok
  7. Save as script file (name it and save it where you want it)
  8. Next
  9. Next

Done ✔️

You can open the file now and see that all values are also included now

Upvotes: 16

nos
nos

Reputation: 229058

If you want to generate a .sql file:

  • right click on the database in SQL Server Management Studio
  • select Tasks->Generate Scripts... .
  • In the dialog that pops up, Select All on the screen that says "Select database objects to script"

The generated .sql file can be opened again in SQL Server Management Studio and be run. If the .sql file is very large, see How do you import a large MS SQL .sql file?

Upvotes: 5

Tim Coker
Tim Coker

Reputation: 6524

Also, if you just want to move a database to a new server, Detach/Attach is a quicker option. Backup/Restore is what you want if you want to keep the original database in place.

Upvotes: 2

Icemanind
Icemanind

Reputation: 48686

The best way to do this is to backup the database. This will backup to one file. Then take that file to your new server and do a restore. This should restore everything, from tables to stored procedures to foreign keys and all the data.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332521

Using SQL Server Management Studio, you use Backup/Restore feature. The Restore process is laid out in the MSDN documentation. And here's the MSDN article for backing up a database...

Obviously, you restore to another SQL Server database instance (a "database instance" can contain multiple databases.). The version of the instance can be newer than the version the backup came from - the compatibility level will just be set accordingly.

Upvotes: 7

Related Questions