Create a Full Database Backup In SQL Server Using SSMS and T-SQL
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
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.
After clicking on the Add button set backup destination and the name of the backup file and then click Ok
Now again, click on the Ok button to create the SQL Server database backup you will see a similar screen as shown below
Create SQL database Backup using Transact-SQL
To create a full database backup, execute the following statement
To create a transaction log backup, execute the following statement
This is basically all you need to do to create a backup of your SQL Server database.
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