In SQL Server, a backup, or data backup is a copy of computer data that is created and stored in a different location so that it can be used to recover the original in the event of a data loss. To create a full database backup, the below methods could be used :
1. Using the SQL Server Management Studio in Microsoft SQL Server.
2. Using the Transact-SQL:
- Connect to the Database Engine.
- Open New Query
Syntax
BACKUP DATABASE databasename TO backup_device [ ] [ WITH with_options [] ] ;
Where,
- databasename is the database that needs to be backed up.
- backup_device [ DISK | TAPE ] declares a list of backup devices from 1 to 64 to be used for the backup operation.
- WITH with_options [ ] defines one or more options mentioned below:
- COMPRESSION | NO_COMPRESSION defines whether backup compression is performed on this backup or not.
- DESCRIPTION could have a maximum of 255 characters and describes the backup set.
- NAME could have a maximum of 128 characters and describes the name of the backup set.
- FORMAT [MEDIANAME] [MEDIADESCRIPTION] could be used while using media for the first time or to overwrite all existing data.
Below are various options for creating backups:
1. Create a full SQL Server backup to disk:
We can create a full SQL Server backup to disk using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName.BAK'
GO
Here, BACKUP DATABASE DatabaseName is the instruction. The “TO DISK” option specifies that the backup should be written to drive, as well as the backup location and filename.
2. Create a differential SQL Server backup:
We can create a differential SQL Server backup using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName.BAK'
WITH DIFFERENTIAL
GO
3. Create a file-level SQL Server backup:
We can create a file-level SQL Server backup using the following statement:
BACKUP DATABASE DatabaseName FILE = 'DatabaseName'
TO DISK = 'C:\DatabaseName_DatabaseName.FILE'
GO
Here, “WITH FILE” option is used to define a file backup in this command. The logical filename of the database must be defined.
4. Create a filegroup SQL Server backup:
We can create a filegroup SQL Server backup using the following statement:
BACKUP DATABASE DatabaseName FILEGROUP = 'ReadOnly'
TO DISK = 'C:\DatabaseName.FLG'
GO
5. Create a full SQL Server backup to multiple disk files:
We can create a full SQL Server backup to multiple disk files using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK',
DISK = 'D:\DatabaseName_2.BAK',
DISK = 'E:\DatabaseName_3.BAK'
GO
Here, instead of one big file, this command uses the “DISK” option several times to write the backup to three smaller files of similar size.
6. Create a full SQL Server backup with a password:
We can create a full SQL Server backup with a password using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK'
WITH PASSWORD = 'Q!W@E#R$'
GO
Here, “WITH PASSWORD” command produces a backup with a password that must be entered when the database is restored.
7. Create a full SQL Server backup with progress stats:
We can create a full SQL Server backup with progress stats using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK'
WITH STATS
GO
Here, “WITH STATS” command generates a complete backup and also shows the backup’s progress. The default setting is to display improvement every ten percent.
Here is another sample for showing stats after every 2%.
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK'
WITH STATS = 2
GO
8. Create a SQL Server backup and give it a description:
We can create a SQL Server backup and give it a description using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK'
WITH DESCRIPTION = 'Full backup for DatabaseName'
GO
To give the backup a name, “WITH DESCRIPTION” command uses the description option. This can be used for any of the restore commands in the future to see what’s in the backup. The maximum number of characters is 255.
9. Create a mirrored SQL Server backup:
We can create a mirrored SQL Server backup using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK'
MIRROR TO DISK = 'D:\DatabaseName_mirror.BAK'
WITH FORMAT
GO
This choice allows you to make several backup copies, ideally in different locations.
10. Specifying multiple options for SQL Server Backups:
We can specify multiple options for SQL Server Backups using the following statement:
BACKUP DATABASE DatabaseName
TO DISK = 'C:\DatabaseName_1.BAK'
MIRROR TO DISK = 'D:\DatabaseName_mirror.BAK'
WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$'
GO
Example:
Creating Database:
CREATE DATABASE GeeksDB;
GO
To use this database:
USE GeeksDB;
GO
Backup this database:
BACKUP DATABASE GeeksDB
TO DISK = 'D:\Backup\GeeksDB.bak'
WITH FORMAT,
MEDIANAME = 'GeeksDBBackup',
NAME = 'Full Backup of GeeksDB';
GO
Output:

Similar Reads
Dynamic SQL
Dynamic SQL is a powerful SQL programming technique that allows us to construct and execute SQL statements at runtime. Unlike static SQL, where queries are fixed during the development phase, dynamic SQL enables developers to build flexible and general-purpose SQL queries that adapt to varying condi
5 min read
SQL - SELECT AS
In SQL, the SELECT AS clause is an essential feature that helps improve query readability and makes our database results more understandable. By aliasing columns and tables, we can provide meaningful names to our output, making complex queries easier to interpret and manage. In this article, we will
4 min read
Basic SQL Commands
Structured Query Language (SQL) is the standard language used for managing and interacting with relational databases. Whether we are retrieving data, updating records, or defining the structure of our data, SQL commands provide a powerful and flexible way to handle these tasks. This article will exp
6 min read
SQLite Group By Clause
SQLite is a server-less database engine and it is written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is to escape from using complex database engines like MYSQL etc. It has become one of the most popular database engines as we
6 min read
SQL - SELECT LAST
SELECT LAST is a concept or function often used to describe retrieving the last record or last row from a table in SQL. Although MS Access supports a LAST() function to directly fetch the last value from a column, this function is not universally supported across all SQL-based databases. Instead, in
5 min read
SQL Select Database
The USE DATABASE statement is a command in certain SQL-based database management systems that allows users to select and set a specific database as the default for the current session. By selecting a database, subsequent queries are executed within the context of that database, making it easier to i
3 min read
SQL | Aliases
In SQL, aliases are temporary names assigned to columns or tables for the duration of a query. They make the query more readable, especially when dealing with complex queries or large datasets. Aliases help simplify long column names, improve query clarity, and are particularly useful in queries inv
4 min read
SQL Natural Join
Natural join is an SQL join operation that creates a join on the base of the common columns in the tables. To perform natural join there must be one common attribute(Column) between two tables. Natural join will retrieve from multiple relations. It works in three steps. In this article, we will disc
3 min read
SQL Literals
There are four kinds of literal values supported in SQL. They are : Character string, Bit string, Exact numeric, and Approximate numeric. These are explained as following below. Character string : Character strings are written as a sequence of characters enveloped in single quotes. the only quote ch
1 min read
SQL Comments
SQL comments play an essential role in enhancing the readability, maintainability, and documentation of our SQL code. By using comments effectively, developers can provide context, clarify complex logic, and temporarily disable parts of the code. Whether we're working alone or collaborating with a t
4 min read
SQL - Show Databases
In the dynamic scene of database management, having a good insight into the available databases for effective administration and development tasks. SHOW DATABASES command is designed to present all databases located on the server. The purpose of exploring the SQL SHOW DATABASES command is to give da
3 min read
MySQL Aliases
MySQL server is an open-source relational database management system that is a major support for web-based applications. Databases and related tables are the main components of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly
4 min read
SQL Operators
SQL operators are important in database management systems (DBMS) as they allow us to manipulate and retrieve data efficiently. Operators in SQL perform arithmetic, logical, comparison, bitwise, and other operations to work with database values. Understanding SQL operators is crucial for performing
5 min read
MySQL WHERE Clause
The MySQL WHERE clause is essential for filtering data based on specified conditions and returning it in the result set. It is commonly used in SELECT, INSERT, UPDATE, and DELETE statements to work on specific data. This clause follows the FROM clause in a SELECT statement and precedes any ORDER BY
5 min read
SQL Injection
SQL Injection is a security flaw in web applications where attackers insert harmful SQL code through user inputs. This can allow them to access sensitive data, change database contents or even take control of the system. It's important to know about SQL Injection to keep web applications secure. In
7 min read
SQL Server Architecture
Microsoft SQL Server is a widely used relational database management system (RDBMS) that organizations around the world rely on for managing and processing their data. It provides a scalable and reliable platform for managing large volumes of data, supporting a wide range of applications from small-
5 min read
SQL CREATE DATABASE
The CREATE DATABASE statement is a foundational SQL command used to create new databases in SQL-based Database Management Systems (DBMS), including MySQL, PostgreSQL, SQL Server, and others. Understanding how to use this command effectively is crucial for developers, database administrators, and any
4 min read
SQL Concepts and Queries
In this article, we will discuss the overview of SQL and will mainly focus on Concepts and Queries and will understand each with the help of examples. Let's discuss it one by one. Overview :SQL is a computer language that is used for storing, manipulating, and retrieving data in a structured format.
5 min read
SQLite WHERE Clause
SQLite is the serverless database engine that is used most widely. It is written in c programming language and it belongs to the embedded database family. In this article, you will be learning about the where clause and functionality of the where clause in SQLite. Where ClauseSQLite WHERE Clause is
3 min read