Find the best tutors and institutes for MS SQL Administration

Find Best MS SQL Administration Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

What is database backup? How backup works in SQL Server?

06/08/2018 0 0

Database Backup in normal terms can be said as a copy of data which can be used in a situation when the things go wrong with your system/server. If your role is IT/Database Administrator, then it is your responsibility to safeguard the data from any disaster apart from critical security.

MS SQL Server, unlike another database system, has the ability to take the backup which can be used during disaster recovery. SQL Server allows the database user to decide and take backup based on the business strategy (RPO) Recovery point objective and (RTO) Recovery Time Objective. 

Based on the fact to recover the database till the point of recovery SQL Server has a different type of database recovery model and backup type.

  • Full or Database
  • Differential or Incremental
  • Transactional Log or Log.

Full database backup – Backs up all the data and objects in the data file(s) for a given database.

Differential database backup – Backs up any data and objects in the data file(s) for a given database that have changed since the last full backup. Remember that a differential backup backs up data since the last full backup, even if there have been intervening differential backups.

Transaction log backups – Copy into a backup file all the log records inserted into the transaction log LDF file since the last transaction log backup.

Apart from above type of backup. SQL Server has below backups 

  • File and filegroup backups - File and filegroup backups back up individual database files and filegroups rather than performing a full database backup. This method backs up very large databases. You must back up the transaction log when performing a file and filegroup backup. You cannot use this method if the Truncate Log On Checkpoint option is enabled.
  • Copy-only backups - Copy-only backups are functionally the same as a full database or transaction log backups but do not affect the backup sequence. For example, if you took a full backup, a copy backup, and then a transaction log backup, all the transaction logs since the full backup would be backed up, and the existence of the copy backup would be ignored. Copy backups cannot be used as the basis for a differential backup or transaction log backup.

Happy Learning


0 Dislike
Follow 2

Please Enter a comment


Other Lessons for You

What is DBMS and RDBMS
Database Management Systems A database is a collection of data or records. Database management systems are designed to work with data. A database management system (DBMS) is a software system that uses...

Amit Patil | 15 Apr

0 0

What's the best way to learn SQL Server?
Just read the book, type and run the code, and work the chapter examples. If you work the book, you will be ahead of many SQL Server programmers. You’ll understand declarative set-based interaction...

MS-SQL Database
1. Select: 01-Select All Given a City table, whose fields are described as: +-------------+----------+ | Field | Type | +-------------+----------+ | ID | int(11) | | Name ...

Prashant Kumar | 27/02/2018

0 0

Write A Query To Get Nth Highest Salary
WITH CTE AS ( SELECT EmpID, EmpName, EmpSalary, RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC) FROM dbo.Salary ) SELECT EmpID, EmpName, EmpSalary FROM CTE WHERE RN = @NthRowUse...

Rakesh Pattanayak | 11/01/2018

0 0

What Is WorkTable In SQL Server?
What is WorkTable in SQL Server? USE AdventureWorks2014 GO SET STATISTICS IO ON GO SELECT * FROM Production.Product p CROSS JOIN Production.Product p1 GO If you run above query,...

Amitava Majumder | 19/12/2017

0 0

Find Best MS SQL Administration Training?

Find Now » is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on, to fulfill their learning requirements across 1,000+ categories. Using, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more