Backup Database in MS SQL Server

Prerequisite – Create database in MS SQL Server

To Create a Full Database Backup, the below methods could be used –

• SQL Server Management Studio.
• Transact-SQL.

Restriction :
Backups created on a newer version of SQL Server cannot be restored in previous versions of SQL Server.

Facts to know :

• When the database size increases, full database backups require more time and more storage space to complete.
• The sp_spaceused system stored procedure could be used to estimate the size of a full database backup.
• An entry is added for every successful backup in the SQL Server error logs and in the system event log.

Permissions :

• BACKUP DATABASE permissions to members of the sysadmin server role and the db_owner and db_backupoperator to database roles.
• The account under which the SQL Server service runs must have write permissions to the backup device, so the SQL Server service must be able to read and write to the device.

Using SQL Server Management Studio :

1. In Object Explorer, connect to the desired instance of the Microsoft SQL Server Database Engine, expand the server instance.
2. Expand Databases box and select a user database or select a system database.
3. Right-click the database that need to backup, click on Tasks, and then click Back Up….

   
   
   

4. In the Back-Up Database dialog box, the database that you selected appears in the drop-down list.
   • In the Backup type drop-down list, select the backup type – the default is Full.
   • Under Backup component, select Database.
   • Review the default location for the backup file, in the Destination section.
   • To remove a backup destination, click on it and Remove.
   • To backup to a new device, change the selection using the Add and select destination.

   
   
   
5. Review the other available settings under the Media Options and Backup Options pages.

   
   
   

6. Click OK to start the backup. Click OK to close the SQL Server Management Studio dialog box once the backup completed successfully.

   
   
   

Using 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 need 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.

Example-1 :
Back up database to a disk device.

USE GeekDB;
GO
BACKUP DATABASE GeekDB
TO DISK = 'D:\Backup\GeekDB.bak'
WITH FORMAT,
MEDIANAME = 'GeekDBBackup',
NAME = 'Full Backup of GeekDB';
GO

Example-2 :
Back up to a tape device.

USE GeekDB;
GO
BACKUP DATABASE GeekDB
TO TAPE = '\\.\TapeLocation'
WITH NOINIT,
NAME = 'Full Backup of GeekDB';
GO