SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

How to Backup Database in SQL Server

How to Create a Full Backup of Database in SQL Server

This article explains the steps you need to follow to take a FULL Backup of Database in SQL Server Using SQL Server Management Studio and by using TSQL Scripts.

The steps mentioned in this article are applicable for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016 and higher.

If you are interested in knowing the steps you need to follow to restore a full database back up in SQL Server then read the following article titled “How to Restore Full Database Backup in SQL Server”.

A Step by Step Guide to Take a Full Database Backup in SQL Server.

Permissions Required to Take Database Backup in SQL Server

To take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.

Error Message

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'DatabaseName'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

TSQL Script – Create Login with DBCREATOR Server Role and DB_OWNER Database Role

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role can take a Full Backup of a database in SQL Server using SQL Server Management Studio, T-SQL Scripts or by using Powershell Commands.

Let’s get started…

How to Take Full Database Backup Using SQL Server Management Studio

Step 1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.

Step 2. Expand Databases node and then right click the database which you want to take a full backup and point to Tasks, and then click Back Up… option as shown in the below snippet to open up Back Up Database dialog box.

How to Take Full Database Backup in SQL Server Using SSMS

Recommendation: Read SQL Server Database Backup Tutorial with Examples to understand database backups in depth whether you are new to SQL Server or an Expert.

Step 3. In Back Up Database Dialog box, in Database List box, verify the database name. Optionally you can even select a different database to backup. You will be able to perform a Full Backup of a database which is FULL, BULK_LOGGED or Simple recovery model. To know more about different recovery models in SQL Server read the following article titled Understanding Different SQL Server Recovery Models.

Step 4. In Backup type list box select Full. Once you have taken a full backup of a database then you can perform Differential backup or Transactional log backups. However, if you database is in Simple recovery model you will not be able to take a Transactional log backup. This is by design from Microsoft.

Step 5. If you want to perform a Copy-Only backup of a database then choose Copy-Only Backup option in Back Up Database Dialog box. Using copy-only backup option you can take a Full, Differential or Transaction Log Backup which is independent of the sequence of conventional SQL Server backups. The copy-only backup option was initial introduced in SQL Server 2005.

Click the below Number Buttons to continue reading the rest of the article.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips