Backup Management

Find your database recovery mode: using system function : DATABASEPROPERTYEX

USE master
go
SELECT DATABASEPROPERTYEX('SREEJITH','RECOVERY');

or Use the below

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = '<DATABASE_NAME' ;
GO

Change the database recovery Model 

ALTER DATABASE <DATABASE_NAME> SET RECOVERY FULL;


Code to create a Media Set on Mutiple Devices.

USE master
GO
BACKUP DATABASE [sreejith] TO
DISK = 'C:\Backup\sreejith_1.bak',
DISK = 'D:\Backup\sreejith_2.bak'
WITH MEDIANAME = 'Sreejith_MediaSet'
GO

FULL BACKUP : Basic syntax to create a Full Backup

BACKUP DATABASE sreejith
TO DISK = 'C:\Backups\sreejith.bak'

Basic Syntax to create a differential database backup

BACKUP DATABASE sreejith
TO DISK = 'C:\Backups\sreejith_diff.bak'
WITH DIFFERENTIAL
GO


Basic syntax to backup your transaction log

BACKUP LOG <database_name>
TO DISK = 'C:\Backups\sreejith.trn'

You can also Backup your database using the graphical user interface provided by the SSMS - SQLSERVER Management Studios

• Full Backup using SQLServer Managaement Studios 

Right click on the database and -->Tasks--> Back Up

Choose the Backup Type in the drop down list, check the recovery model chosen, Click on Add below to choose the backup location and give a file-name as per standards followed by your organization.

Click okay to take the backup

Estimate the size of the FULL backup of the database.

USE <UR_DB_NAME>
go
sp_spaceused

2.12MB  is the estimated size of the full backup. Subtract Datasize - Unallocated space.

PARTIAL BACKUP : Partial backups can only be performed on T-SQL

Create a full partial backup
Syntax :

BACKUP DATABASE <database_name> READ_WRITE_FILEGROUPS
TO DISK = 'C:\<database_name>_Partial.BAK'
GO

Create a differential partial backup
Syntax :

BACKUP DATABASE <database_name> READ_WRITE_FILEGROUPS
TO DISK = 'C:\<database_name>_Partial.DIF'
WITH DIFFERENTIAL

GO

File Backups : - 

Syntax:

BACKUP DATABASE SREEJITH
FILE = 'Sreejith_data'
TO DISK = 'C:\Backups\Sreejith_Data.bak'
GO