SQL Server Database Backups(FULL,LOG,Differential,FILE and FILEGROUP)

STEP 1: Create a database which having 3 filegroup.Primary filegroup is default,other two filegroups are COURSE and STUDENTS.

Use master
go;

CREATE DATABASE University_Database1
ON PRIMARY
(
NAME  =  'UNIVERSITY_DB', FILENAME  =  'C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\UNIVERSITY_DB_PRIMARY.mdf'
)
,
FILEGROUP COURSE
(
NAME  =  'COURSES', FILENAME  =  'C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\COURSES_FILE.mdf', SIZE = 100 MB, MAXSIZE = 300 MB,
FILEGROWTH=1MB
),
FILEGROUP STUDENTS
(
NAME  =  'STUDENTS', FILENAME  =  'C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\STUDENTS_FILE.mdf', SIZE = 100 MB,
 MAXSIZE = 400 MB, FILEGROWTH = 10 MB
)
LOG ON
(
NAME  =  'UNIVERSITY_DB_LOG', FILENAME  =  'C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\UNIVERSITY_DB_LOG.ldf'
)

STEP 2.Taking the Full database backup:

Backup database University_Database1 to disk='C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\University_Database1_FULL.bak'

 with stats=10,Compression

STEP 3.Taking Differential backup: We need to use With DIFFERENTIAL Clause.All Changes since all full backup are backed-up in Differential backup.

Backup database University_Database1 to disk='C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\University_Database1.diff'

with DIFFERENTIAL, stats=10,Compression

STEP 4:  Taking the Log backup: When we take the t-log backup,checkpoint is automatically issued and all committed transaction log records are auto truncated from transaction log file.
 In order to take transaction log backup we use .trn extension.however we can use any extension but Microsoft recommend to use .trn extension.

 Backup Log University_Database1 to disk='C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\University_Database1.trn'

 with stats=10,Compression

STEP 5: Creating File Backup: in order to take File backup, we use FILE clause in the backup command. To Find the Logical file name we use SP_HELP DB_name. 

 Sp_helpdb University_Database1

we will take the backup of FILE "UNIVERSITY_DB"

Backup database University_Database1  file='UNIVERSITY_DB' to disk='C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\University_Database1.FIL' 

 with compression,stats=10

STEP 6:Creating FILEGROUP Backup:

Right CLick Database>>TASKS>>Backup,it opens a new window where we need to check the "FILES AND FILEGROUPS" radio button then select the FileGroups which need to be backed up.

> FileGroup Backup using T-SQL: Get the filegroup details using below T-SQL command.

use [University_Database1]

go

select * from sys.filegroups

Now we want to backup "COURSE" FileGroup.

BACKUP DATABASE [University_Database1] 

FILEGROUP = N'COURSE' TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SERVER1\MSSQL\DATA\University_Database1.FIL' WITH NOFORMAT,   

STATS = 10

GO

How to take Backup of more than one File-groups:

BACKUP DATABASE University_Database1
FILEGROUP = ('COURSE','STUDENTS')
TO
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SERVER1\MSSQL\DATA\University_Database1FGBackupD.BAK'
WITH compression,stats=10

PARITAL BACKUPS: A mechanism to Backup all  "READ WRITE FILEGROUPS" OF THE DATABASE.

In order to understand it, we will make READ-ONLY File-group in the database and see if READ-ONLY File-group is backed-up or not.

--We Modify Students fileGroup to READ only

ALTER DATABASE [University_Database1] MODIFY FILEGROUP [STUDENTS] READONLY

Now take the Partial Backup using below command:


BACKUP DATABASE  [University_Database1]
READ_WRITE_FILEGROUPS
TO
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SERVER1\MSSQL\DATA\\PartialBackup.BAK'

Only READ WRITE File-groups are backed up, Read-only File-Group "STUDENTS" is not included in backup.

Differential Partial Backup: We can take the Differential partial backup and it's base will be Partial backup.Differential partial backup will backup all the changes in the Read write files since the recent partial backup.We only have to use WITH DIFFERENTIAL Clause.

BACKUP DATABASE  [University_Database1]
READ_WRITE_FILEGROUPS
TO
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SERVER1\MSSQL\DATA\\PartialDifferentialBackup.BAK'
With DIFFERENTIAL

• Tweet
• Share
• Share
• Share
• Share