Trending SQL Server Tips

How to Backup Database in SQL Server - Create a Full Back up of Database

Feb 20, 2012
Page: 1/3


In this article we will take a look at the steps you need to follow to take a full backup of SQL Server Database. The steps mentioned in this article are applicable for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. 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 Database in SQL Server”.

"This article has a total of 3 pages including this page. Click the links below to open all 3 pages in a seperate window to learn How to Back up database in SQL Server. Page 1, Page 2, Page 3"

Permissions Required to Take Database Backup in SQL Server

In order 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.

Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role

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

Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role can take a full backup of database using either:-

• Using SQL Server Management Studio
• Using TSQL scripts

Let us take a look at both the above mentioned options to perform a full backup of a database in SQL Server.

Take Full Backup of a Database Using SQL Server Management Studio

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

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 backup Database Using SQL Server Management Studio - Perform Full Database Backup

SQL Server Database Backup Options and Commands a Step by Step Tutorial with Examples

This tutorial will give you compressive information on different database backup options which is available to a DBA or a Developer within SQL Server. The detailed topics mentioned in this tutorial will help you understand database backups in depth whether you are new to SQL Server or an Expert.  Continue Reading...

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 SQL Server Recovery Model.

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.

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 transactional log backup which is independent of the sequence of convential SQL Server backups. The copy-only backup option was introduced in SQL Server 2005.

6. In Backup Component select Databases and in Backup set leave the default Name and optionally enter Desciption. Leave the default value as 0 days for Backup set will expire after option.

Backup Database Dialog Box in SQL Server Management Studio

Click on Next Page to continue reading rest of the article…

Next Page..

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."