Import / Export database with SQL Server Server Management Studio

Question

I thought this would be trivial, but it isn't... I'm sure there is a simple way to do it but I can't manage to find it. Shame on me.

I want to import/export the database itself, the tables, the constraints (foreign keys and so on). I'd rather not get the data with it, but I can get rid of it after if there's no other way.

So... how do you export a database using MS SQL Server Management Studio ? How do you import it?

The only solution I found was right click on the tables and "script to Create", but I have something like 100 tables, so I'd rather avoid this.

Thanks!

Latt

Answer 1

Right click the database itself, Tasks -> Generate Scripts...

Then follow the wizard.

For SSMS2008+, if you want to also export the data, on the "Set Scripting Options" step, select the "Advanced" button and change "Types of data to script" from "Schema Only" to "Data Only" or "Schema and Data".

Norikonorina

how about with getting the data? is it possible to create insert queries with a method like generate script ? – Pounce

@Kubi, on the "Set Scripting Options" step, select the "Advanced" button and change "Schema Only" to "Data Only" or "Schema and Data". – Norikonorina

@Brandon, thank you for that. Why "schema only", "data only" or "schema and data" is considered an "Advanced" option is beyond my comprehension. – Mcgrath

2Brandon: "Advanced" scripting options are only available in SSMS 2008+ . – Mizell

"Data Only" or "Schema and Data" create INSERT commands that duplicate the header names for each row. The file is about twice the size of a text version of the data. One could try instead to export the schema only, then use BULK INSERT to load a csv file created with the export... wizard. Be careful, MSQL doesn't know how to read quoted fields. – Maple

So how do you import it afterwards? – Endothecium

Answer 2

Another solutions is - Backing Up and Restoring Database

Back Up the System Database

To back up the system database using Microsoft SQL Server Management Studio Express, follow the steps below:

1. Download and install Microsoft SQL Server 2008 Management Studio Express from the Microsoft web site: http://www.microsoft.com/en-us/download/details.aspx?id=7593

2. After Microsoft SQL Server Management Studio Express has been installed, launch the application to connect to the system database. The "Connect to Server" dialog box displays. In the "Server name:" field, enter the name of the Webtrends server on which the system database is installed. In the "Authentication:" field select "Windows Authentication" if logged into the Windows machine using the Webtrends service account or an account with rights to make changes to the system database. Otherwise, select "SQL Server Authentication" from the drop-down menu and enter the credentials for a SQL Server account which has the needed rights. Click "Connect" to connect to the database.

3. Expand "Databases", right-click on "wt_sched" and select "Tasks" > "Back Up..." from the context menu. The "Back Up Database" dialog box displays. Under the "Source" section, ensure the "wt_sched" is selected for the "Database:" and "Backup type:" is "Full." Under "Backup set" provide a name, description and expiration date as needed and then select "Add..." under the "Destination" section and designate the file name and path where the backup will be saved. It may be necessary to select the "Overwrite all existing backup sets" option in the Options section if a backup already exists and is to be overwritten.

4. Select "OK" to complete the backup process.

5. Repeat the above steps for the "wtMaster" part of the database.

Restore the System Database

To restore the system database using Microsoft SQL Server Management Studio, follow the steps below:

1. If you haven't already, download and install Microsoft SQL Server 2008 Management Studio Express from the Microsoft web site: http://www.microsoft.com/en-us/download/details.aspx?id=7593

2. After Microsoft SQL Server Management Studio has been installed, launch the application to connect to the system database. The "Connect to Server" dialog box displays. In the "Server type:" field, select "Database Engine" (default). In the "Server name:" field, select "\WTSYSTEMDB" where is the name of the Webtrends server where the database is located. WTSYSTEMDB is the name of the database instance in a default installation. In the "Authentication:" field select "Windows Authentication" if logged into the Windows machine using the Webtrends service account or an account with rights to make changes to the system database. Otherwise, select "SQL Server Authentication" from the drop-down menu and enter the credentials for a SQL Server account which has the needed rights. Click "Connect" to connect to the database.

3. Expand "Databases", right-click on "wt_sched" and select "Delete" from the context menu. Make sure "Delete backup and restore history information for databases" check-box is checked.

4. Select "OK" to complete the deletion process.

5. Repeat the above steps for the "wtMaster" part of the database.

6. Right click on "Databases" and select "Restore Database..." from the context menu. In the "To database:" field type in "wt_sched". Select the "From device:" radio button. Click on the ellipse (...) to the right of the "From device:" text field. Click the "Add" button. Navigate to and select the backup file for "wt_sched". Select "OK" on the "Locate Backup File" form. Select "OK" on the "Specify Backup" form. Check the check-box in the restore column next to "wt_sched-Full Database Backup". Select "OK" on the "Restore Database" form.

7. Repeat step 6 for the "wtMaster" part of the database.

Courtesy - http://kb.webtrends.com/articles/How_To/Backing-Up-and-Restoring-the-System-Database-using-MS-SQL-Management-Studio

Lietuva

The problem I've had with this approach is that the bak file doesn't behave nicely when restoring in another computer. Sometimes it works, sometimes it doesn't. At least for me the script is a safer approach. +1 for the detailed explanation – Cardigan

also, this requires system privileges, which often if you're developing on shared services infrastructure you don't have. well done Microsoft. – Cassel

Answer 3

Exporting and Importing Database with MS SQL Server Management Studio

1.Exporting Database from SQL Server

1. On Object Explorer right click database you want to export listed in Databases node.

2. Select Tasks then select Export Data-tier Application

3. Click Export Settings

4. Make sure Save to local disk is checked

5. Browse the path and save the file as .bacpac file

6. Click Next and Next to export

2.Importing Database to SQL Server

1. On Object Explorer right Databases node.

2. Select Import Data-tier Application

3. Click Import Settings

4. Make sure Import from local disk is checked

5. Browse the path and open the .bacpac file

6. Click Next and Next to import

Hardi

Answer 4

I wanted to share with you my solution to export a database with Microsoft SQL Server Management Studio.

To Export your database

1. Open a new request
2. Copy paste this script

DECLARE @BackupFile NVARCHAR(255);
SET @BackupFile = 'c:\database-backup_2020.07.22.bak';
PRINT @BackupFile;
BACKUP DATABASE [%databaseName%] TO DISK = @BackupFile;

Don't forget to replace %databaseName% with the name of the database you want to export.

Note that this method gives a lighter file than from the menu.

To import this file from SQL Server Management Studio. Don't forget to delete your database beforehand.

1. Click restore database

2. Add the backup file

3. Validate

Enjoy! :) :)

Mesoblast

finally some screenshot solution ! :) – Leptophyllous

screenshots solution :) Thumbs Up – Metaphosphate

Answer 5

for Microsoft SQL Server Management Studio 2012,2008.. First Copy your database file .mdf and log file .ldf & Paste in your sql server install file in Programs Files->Microsoft SQL Server->MSSQL10.SQLEXPRESS->MSSQL->DATA. Then open Microsoft Sql Server . Right Click on Databases -> Select Attach...option.

Antiperspirant

I tried this method and it worked. Copied database.mdf and database_log.ldf from the above mentioned folder (SQL 2012), then transferred those 2 files to a new server with different version (SQL Express 2014) of SQL Management Studio. – Baroscope

Answer 6

I tried the answers above but the generated script file was very large and I was having problems while importing the data. I ended up Detaching the database, then copying .mdf to my new machine, then Attaching it to my new version of SQL Server Management Studio.

I found instructions for how to do this on the Microsoft Website:
https://msdn.microsoft.com/en-us/library/ms187858.aspx

NOTE: After Detaching the database I found the .mdf file within this directory:
C:\Program Files\Microsoft SQL Server\

Hartzell

Answer 7

Using Server Management Studio (SSMS) Import and Export Wizard is straightforward. You can do dump the whole DB directly into another database, no need to store it into a temp file.

> In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.

> Expand Databases.

> Right-click a database.

> Point to Tasks.

> Click one of the following options.

• Import Data
• Export Data

Reference: MS docs Import and Export Wizard

Fleming