Learn MS SQL Administration from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

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

Microsoft Excel
Software developed and manufactured by Microsoft Corporation that allows users to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and columns. Microsoft...

Microsoft Word
Microsoft Word is a widely used commercial word processor designed by Microsoft. Microsoft Word is a component of the Microsoft Office suite of productivity software, but can also be purchased as a stand-alone...

What Is Phython?
Python is a general-purpose interpreted, interactive, object-oriented, and high-level programming language. It was created by GuidovanRossum during 1985- 1990. Like Perl, Python source code is also available...

Understanding Indexes In SQL Server
Indexes in SQL Server are created on columns in tables or views. The index provides a faster way to look up data based on the values in those columns.For example, if you create an index on the primary...

Cursors In SQL Server
First thing first Usage of Cursors is not encouraged in SQL Server as they are slow. You may go with While loop if you need to iterate through a recordset. Cursor is a database object to retrieve data...

Looking for MS SQL Administration Classes?

The best tutors for MS SQL Administration Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn MS SQL Administration with the Best Tutors

The best Tutors for MS SQL Administration Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. 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