Import / Export database with SQL Server Server Ma

问题:

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!

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

回答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

回答3:

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.

回答4:

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\

回答5:

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

   SET @BackupFile = 'D:\database-backup.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! :) :)