Backup SQL Database In SQL Server Management Studio

In this article, we are going to take SQL database backup in the SQL server management studio using different ways.

There are different ways to take SQL database backup in SQL server management studio. I am going to give the basic information on a few of them.

1. Full back up to disk (.bak file)

2. Export a Data-tier Application

3. Generate Script (.SQL file)

Now let’s see each one of them step by step.

1. Full back up to disk (.bak file)

in this, we are going to take a full backup of the database into our disk. which will be saved as. bak format.

this .bak file will be restored later. you just need to select which backup you wanted to restore at restoration time.

following are steps for taking .bak file backup.

1. Connect the instance of the Microsoft SQL Server Database, in the Object Explorer, expand its server tree.

2. Now expand the Databases, right-click on the database which you wanted to take backup, here it’s CodesHubDemoDB, hover to Tasks, and then select Back Up.it will open one dialog box.

3. In the backup dialog box, in the destination division you can add the actual file path where you wanted to take backup. on click add it will open another window where you can select folder path.

4. Now After selecting the folder Click OK to start the backup. One dialog box appears when the backup successfully completed. click ok to close it.

Now we are going to check the .bak file in the folder which we have selected.

So this way we can take database backup in the .bak file.

2. Export a Data-tier Application

in this, we are going to take a full backup of the database into our disk. which will be saved in .bacpac format.

this .bacpac file will be imported later. you just need to select which backup you wanted to import at importing time.

following are steps for taking .bacpac  file backup.

1. Connect the instance of the Microsoft SQL Server Database, in the Object Explorer, expand its server tree.

2. Now expand the Databases, right-click on the database which you wanted to take backup, here it’s CodesHubDemoDB, hover to Tasks, and then select Export a Data-tier Application. it will open one dialog box.

3. In the backup dialog box, select next, after that in setting select Save to local disk option and browse for the actual file path where you wanted to take backup. on click browse it will open another window where you can select folder path. once you select the appropriate path then click on the Next button.

4. Now on the summary step. it will show you a summary. here you need to check and click on the Finish button to start the backup process. it will take you to Result step where you can see progress of backup process. once all is finished it will show you the operation complete status. Click on the close button to close the dialog box.

Now we are going to check the .bacpac file in the folder which we have selected.

So this way we can take database backup in the .bacpac file.

3. Generate Script(.SQL file)

in this, we can take a full backup, and/or selected entities (like tables, SPs, etc) of the database into an SQL Script file. which will be saved in .SQL format.

This .SQL file will be Executed later. you just need to execute which SQL you wanted to import/execute at the time of Executing/importing.

in this, we have options for Types of data to script. we can select one of these options. Schema only or Schema and Data or Data only.

following are steps for taking/generating  .SQL  script file as a backup.

1. Connect the instance of the Microsoft SQL Server Database, in the Object Explorer, expand its server tree.

2. Now expand the Databases, right-click on the database which you wanted to take backup, here it’s CodesHubDemoDB, hover to Tasks, and then select Generate Scripts…. it will open one dialog box.

3. In that dialog box, first you can see the introduction tab, we can simply click on the Next button.

4. Now on the next step, we need to Choose Objects. basically for which object we wanted to generate. here we will get 2 options. like Script for the entire database and its object or for the selected specific database object.

as you can see in the above image we can select the entire database.

as you can see in the above image we can select the specific database object. like tables.

5. Now in the next step, we can Choose the scripting option. basically which type of script we wanted to generate. here we will get 3 options. like Schema only, Schema and Data, Data only.

as you can see in the above image we need to select save to file and chose the actual file path where we wanted to save/generate SQL file.

after that click on the Advanced button. it will open one dialog box.

in that dialog box, in the Types of data script option, we are going to select Schema and Data as we wanted Schema and data both. you can select according to your need like Schema only, Data only. 

6. Now in the next step, it will go on the summary tab also click on the next step so it will start generating the script and you can see progress on that tab.

as you see in the above images. once all progress has been done then you can click on the Finish button.

Now we are going to check the .SQL file in the folder which we have selected.

So this way we can take database backup using generate the .SQL script file.

Hope it will be helpful for you. Thank You 🙂

Also check, Generate C# Model Class From Table In MSSQL