Database Import and Export in SQL Server



SQL Server Management Studio (SSMS) is a popular tool used by database administrators and developers to manage SQL Server databases. One of the essential tasks in database management is importing and exporting databases between servers. In this blog, we will discuss how to import and export databases using SSMS, step by step.

Prerequisites

Before we begin, you need to have the following:

·       SQL Server Management Studio installed on your system.

(Tutorial for downloading SSMS here: (https://www.youtube.com/watch?v=3ayEEbuxddI)

·       Access to the source and destination servers.

·       Appropriate permissions to access and modify databases.

Importing a Database

To import a database using SSMS, follow these steps:

·       Launch SSMS and connect to the destination server.

·       Right-click on the Databases folder and select "Import Data-tier Application."

·       In the "Import Data-tier Application Wizard" window, click "Next."

·       Choose the "Import from a file" option and click "Next."

·       Browse to the location of the .bacpac file that you want to import and select it.

·       Click "Next."

·       In the "Database Settings" window, choose the database name, file paths, and collation, and click "Next."

·       Review the summary information and click "Finish" to start the import process.

Once the process completes, you can access the imported database from the SSMS Object Explorer.

 

 

Step by Step guide to How to create a backup of database

       i.          Open SSMS in your system




    ii.          Connect your Database Engine




   iii.          Open Object Explorer from View menu ribbon or press F8.

(Note: In case the Object Explorer is not open by default, follow these steps.)




   iv.          Open Database Engine




     v.          Open Databases folder




   vi.          Right-click on the database for which you want to create a backup. And you see the following options.





 vii.          Hover your mouse pointer over the "Task" option, and another window will pop up. From there, click on the "Back Up" option.


 

viii.          Once the pop-up window appears, select database, backup type, backup component, and destination.

 


Select file destination in your local system


   ix.          Then Click on OK for further process




·       After successfully creating a backup of our database, a pop-up message will be displayed.



·       Now that we have created a backup of our database, let's proceed to restore the database from the previously created backup.

 

Exporting a Database

To export a database using SSMS, follow these steps:

·       Launch SSMS and connect to the source server.

·       Right-click on the database you want to export and select "Tasks" > "Export Data-tier Application."

·       In the "Export Data-tier Application Wizard" window, click "Next."

·       Choose the "Export to a BACPAC file" option and click "Next."

·       Specify the file location and name for the BACPAC file and click "Next."

·       In the "Database Settings" window, select the objects you want to export, such as tables, views, stored procedures, etc., and click "Next."

·       Review the summary information and click "Finish" to start the export process.

 

Once the process completes, you can copy the exported BACPAC file to the destination server and import it using the steps mentioned earlier.

 

Step by Step guide: How to restored databaseà

       i.          Open Object Explorer and then open Database Engine

 



     ii.          Open Databases folder



   iii.          If you right-click on the "Databases" folder, you will see several options. Please select "Restore Database" from the list.

 


   iv.          This popup window appear just after clicking Restore Databases


Please select the source by browsing your local inventory.

After clicking on more option this popup window appear, add backup .bak file from your local system.

 

Find your “.bak” file from local inventory.



 Select backup database file and then press “OK”.

 


   ix.          Hit the “OK” buttonThen its start the processing to Restored Databases

 

   xi.          See in Restore Plan , Backup sets to restore

 

xii.          If you wish to change the name of the database, you can edit it under the "Destination" option in the database.

 

 

xiii.          Otherwise press “OK”

 

 

xiv.          Here is we have successfully Restored Our Database 

Conclusion

In this blog, we have discussed how to import and export databases using SQL Server Management Studio. These steps can help you move databases between servers quickly and easily. It is essential to ensure that you have the necessary permissions and access to both servers before performing these actions.

 

 

____________________________________________________