Home » SQL Transaction Log » Create a Full Database Backup In SQL Server Using SSMS and T-SQL

Create a Full Database Backup In SQL Server Using SSMS and T-SQL

Andrew Jackson ~ Modified: December 7th, 2015 ~ SQL Transaction Log ~ 2 Minutes Reading

Introduction

This article describes how to create a full database backup in SQL Server using SSMS (SQL Server Management studio) and T-SQL (Transact-SQL). Backup is very important for SQL Server because of the risk of database corruption by network intrusions, hardware failures, power failure or human error. When your database got corrupt then you need a way to recover database from the corrupted state. In such situation, restoration from SQL database backup is one of the best option to recover the database from disaster

Before you Begin

Permission is required to take the backup of the SQL Server database only sysadmin, db_owner and db_backupoperator can take the SQL database backup.

In an explicit or implicit transaction, BACKUP statement is not allowed and you cannot restore the SQL database backup in the earlier versions edition of SQL Server

How to Create a Full Database Backup using SQL Server Management Studio

Open SQL Server Management Studio and connect to the SQL Server Database Engine. Right click on the database which you want to backup and form drop down window click on Tasks and then select Back Up

create sql database backup

Form Back Up Database window, confirm the database name and specify the Backup type: Full. If you check Copy-only Backup dialog box, then you can take full, differential and transaction log backup which is independent of the arrangement of standard SQL Server database backup.

back-up-database-window

After clicking on the Add button set backup destination and the name of the backup file and then click Ok

sql backup destination

Now again, click on the Ok button to create the SQL Server database backup you will see a similar screen as shown below

database backup completed successfully

Create SQL database Backup using Transact-SQL

To create a full database backup, execute the following statement

full backup using t-sql

To create a transaction log backup, execute the following statement

transaction log backup t-sql

This is basically all you need to do to create a backup of your SQL Server database.

Conclusion

SQL Server database backup is the important module of the administrative strategy. In this article I have shown you why SQL Server Database backup is important and how to create SQL database backup using SQL Server Management Studio and Transact-SQL