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!

Solution 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".

Solution 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

Solution 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

Solution 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! :) :)

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

View more solutions

Comments

[Kubi]

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

[赵君君]

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

[Brian Hooper]

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

[ivan_pozdeev]

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

[Nunser]

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

[John Mark]

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

[matao]

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

[Vignesh Chinnaiyan]

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.

[Reven]

finally some screenshot solution ! :)

[Muhammad Hamed K]

screenshots solution :) Thumbs Up