Best (easiest) way to make a SQL Server dump and import that dump in another SQL Server

Question

I would like to achieve a database export (dump) in SQL Server from one server and import that dump in another SQL Server and not necessarily in the same schema name.

For example if I have a database prepared with all the data set for implement a new DB for a new customer, that db is for example named DB_EMPTY

And then I have to setup the same DB on some external server for a customer for example in the schema DB_MY_CUSTOMER

What is the best/simplest way to export (dump) a DB_EMPTY, and import it in DB_MY_CUSTOMER?

Possibly with SQL Server Management Studio?

Solution 1

An easy way would be to use SQL Server Management Studio, in the Object Explorer right click on the database you want to export, select Tasks -> Back Up, then select a destination and file name in the Destination box at the bottom of the dialog. You can play around with the various settings, but you don't need to.

To restore it on another server is basically the opposite, choose Tasks -> Restore -> Database, in the dialog select From Device, then click the browse ellipsis, from there you get a browse dialog, click Add and then navigate to the back up file you created. You can change the databse name in the To database textbox, and control where the files get stored by going to the Options tab and altering the Restore As column entries in the table labelled as Restore the database files as:.

Solution 2

Just in case someone is ending here and noticing "Tasks -> Back up" option does not exists; on recent versions of SSMS ( v18 for example ), you'll need to use "Generate scripts" option and then on "Advanced" select "Schema and Data" on "Types of data to script".

Credits to: http://statmap.co.uk/?page_id=9207