SQL Server Import and Export Wizard can be used to copy data from a source to a destination. source and destination can be sqlserver or any other connection.

Steps to export data in SQL Server 2008:

In Microsoft SQL Server Management Studio, expand the databases.

Right-Click on the database you want to copy to another database->Tasks->Export data/import data.

SQL Server Import and Export Wizard window will open.

Click  Next to continue. Now, you will need to choose a Data Source. You can leave the Data source as SQL Server Native Client 11.0. Enter the Server name. Check windows Authentication  for your database. From the Database dropdown menu, select the name of your database.

Click on Next to continue.

In the Choose a Destination window, you can select a different option to export to Microsoft Access, Microsoft Excel,SQL Server database,etc.,

Then select the destination database,where you want to export your data.

Specify whether to copy tables or to copy the results of a query from the data source.Click Next.

Select source tables and click Next.

Check Run immediately.Click Next.

Click Finish to complete the wizard.

Close the wizard after completing it successfully.