KAILASH'S BLOGS: Backup SQL Database Using SSMS

Introduction

Backups databases is most common task in our development life cycle. A copy of data that can be used to restore and recover the data after a failure. Backups of a database can also be used to restore a copy the database to a new location.

Here in this blog (Backup SQL Database Using SQL Server Management Studio), we are going to discuss how to backup your existing database to local drive and in my next blog, we will discuss how to restore the backed-up database.

Getting Started

Here in this blog, we are not going to discuss the types of databases, here we just discuss how to take a backup of the existing database using SQL Server Management Studio. The Full database backup (Default) will be performed in the demonstration of this blog. The various type of databases we will cover in another blog.

Ensure that you place your database backups on a separate physical location or device from the database files. When your physical drive that stores your database malfunctions or crashes, recoverability depends on the ability to access the separate drive or remote device that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from the same physical disk drive. Carefully study the disk partition and logical volume layouts before choosing a storage location for the backups.

Backup SQL Database Using SQL Server Management Studio

Follow the below steps to backup SQL Database.

Open SQL Server Management Studio and connect to your appropriate SQL Instance.
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, and either select a user database or expand System Databases and select a system database.
Right-click the database that you wish to backup, point to Tasks, and then click Back Up....
In the Back Up Database dialog box, the database that you selected appears in the drop-down list (which you can change to any other database on the server).
In the Backup type drop-down list, select the desired backup type - the default is Full. As mentioned in the above, here we will perform full backup hence select Full in the backup type drop-down list.
Under Backup component, select Database.
In the Destination section, review the default location for the backup file. To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and click Contents.
Review the other available settings under the Media Options and Backup Options pages.

SQL Server Media Options and Backup Options
Click OK to initiate the backup.
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.