Database Backup Procedure in SQL Server

I am discussing the following points:

1. Take Backup using Wizard.
2. Take backup using a generated script.

Let's start.

Take Backup using Wizard

Right-click on the database that you want to take a backup of then go to "Tasks" then go to "Back Up...".



Using the following window you can configure the general tab that contains the backup type. You can change the backup type (Full backup, Differential backup) depending on requirements and you can change the physical location in disk of the backup.

Full backup: Take a full backup of the entire database.

Differential backup: this type of backup captures only the data that has changed since a full backup.



Then configure the Option tab. Go to the Options tab in the same window.

In this window you can select options like append to existing backup set and overwrite all existing backup sets and you can also set the backup compression option to shrink the database backup file.



Finally click the Ok button as shown in the following.



After completion of that you will see this window. This window displays a successful message that indicates the database backup task is completed successfully.


Take backup using a generated script function

1. This type of backup functionality is for when you are unable to restore a backup file on another server database.
   
2. If in your database has a large amount of data then this type of backup functionality is not suitable.

But in some cases a generated script functionality is very useful.

Right-click on the database then select "Tasks" -> "Generate Script".



In this window you can configure multiple things:

1. Select the database object to script.
2. Configure how the script should be saved and published.

Select database object to script: In this tab two options are available, one is to script the entire database object or select some specific database object like table, views, Stored Procedure and so on.



Configure how script should be save and publish: In this tab there are also two options available, one is to save the script to a specific location on your disk and the other is to save to the clipboard or save to a new query window.

Here you can also configure advanced settings using the advanced button.



For the advanced settings go to this window. Here you can select an option according to requirements. Here I am selecting the type of data to script option is schema and data, that means I want to generate a script of database schema and data. You can select an option depending on your own needs. Then click the "OK" button.



After doing all that go to the save or publish tab of this window. Then you can see the script in a new query window depending on your settings. Then click on the "Finish" button.



You can see the output here.



Thanks for reading my article.