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
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.
____________________________________________________
![]() |