SQL Server: how do I export entire database?

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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?