33

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
simonC
  • 4,101
  • 10
  • 50
  • 78
  • 1
    BACKUP and RESTORE are probably the best bet to do this - however, this is **not** programming-related - you should better ask this on [Serverfault.com](http://serverfault.com) where the sysadmins and DBAs hang out – marc_s Feb 17 '11 at 09:44
  • You don't mean schema name (e.g dbo), you mean database name. And as Marc said try serverfault. – Joel Mansford Feb 17 '11 at 12:13

3 Answers3

38

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

slugster
  • 49,403
  • 14
  • 95
  • 145
  • 3
    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 – forlayo Jan 15 '19 at 08:43
  • In SSMS 17.6, in order to restore one database into another, the option to select is under the "Options" tab -→ Restore Options -→ Check "Overwrite the existing database (WITH REPLACE)". If you don't you'll get an error message that says "Microsoft SQL Server Error 3154. The backup set holds a copy of a database other than the existing database on the system." – Sanjiv Jivan Jan 07 '20 at 13:53
13

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

forlayo
  • 1,371
  • 2
  • 13
  • 23
  • You are quite wrong. The latest (GA) version of SSMS is [17.9.1](https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017) (as at 20190115) and it definitely still has the `Back up` option. Maybe you're connecting to a SQL Azure instance (it never has been an option for that)? – slugster Jan 15 '19 at 10:38
  • The latest version is v18 Preview 5; and this one hasn't "Tasks->Back up" menu. Maybe is because, as you're pointing, I am connecting to SQL Azure database. However the solution I am pointing about works for this case. I Hope it would help someone. – forlayo Jan 15 '19 at 10:40
  • Remember that this question was answered in 2011 when SQL Azure didn't exist.... SQL Azure does use a different mechanism, it can be backed up to blob storage as a file from within the Azure portal. – slugster Jan 15 '19 at 10:44
  • 3
    I've added the answer as I've been here looking for this and for me this answer were been useful. :). I am not seeing that the accepted answer is wrong, but it's a good point for those that are coming here with my use case. – forlayo Jan 15 '19 at 11:31
-2

This is called replication : http://databases.about.com/od/sqlserver/ht/distribution.htm

Try to look to the snapshot replication. It is configurable to determine the destination database.

alexl
  • 6,841
  • 3
  • 24
  • 29
  • hmm in my SQL management stuio I cann not see the option of replication on right click – simonC Feb 17 '11 at 11:33
  • 1
    Replication is not available in all editions of SQL Server. Typically you start with a backup on the destination though anyway and then need a reliable connection between both to keep the data in sync. As the OP talks about customer deployment I don't think they want to keep the data in sync – Joel Mansford Feb 17 '11 at 12:15