Skip to main content

Backup & Recovery

In SQL Server, there are three types of backups:

  • Full Backup: Full backup means it will take the backup of the total .mdf file.

  • Differential Backup: Differential backup means it will take the backup of the extent's (8 pages= 1 extent) data which has been changed since last full backup. It is cumulative in nature and so is also called cumulative backup. cumulative means it will take backup from last full backup.

  • T-log Backup: T-log backup means it will backup the transaction log file. It is not cumulative in nature.
Now, there are three Recovery models in SQL server which are defined as:

  • Simple Recovery Model: In this recovery model, we can only have full backup and differential backup. If the data needs to be restored for the database having simple recovery model then last full backup will be restored first and after that the last differential backup. There will be no point in time recovery in this case.

  • Full Recovery Model: In this recovery model, we can have full backup, differential backup and T-log backup. If the data needs to be restored for the database having full recovery model then last full backup will be restored first then based on the requirement we can have differential or T-log restored. Point in time recovery is possible in this case. If we need circular property of the transaction log, we should take Tail log backup which also like T-log only. The only difference is that it will have the transactions upto the last time when database crashed.

  • Bulk Logged: When database is kept in bulk recovery model, it considers the data to be a single transaction. Modified extents will be there in log backup. Recovery for a point in time is possible in bulk logged if the transaction kept in bulk logged has already happened.
Note: You should always leave the database non operational while restore if you want to restore further differential or log backups. Otherwise, it will not allow you to do so.


Comments

Post a Comment

Popular posts from this blog

SQL Server Upgrade Issues

Pre-Requisites We usually think of SQL Server simply as a database engine, while it is a data platform which supports data warehousing, reporting, data integration and data analytics. An in-depth planning is required when upgrading from one version of SQL Server to a newer version along with the features that SQL Server offers. Upgrading these extensive number of features can be remarkably complicated and time-consuming than just upgrading the database engine alone.   Below mentioned are few of the “lessons learned” that help reduce risks of data losses etc.: 1.        Application compatibility: Before planning for upgrade activity, make sure that you discuss the compatibility of SQL Server and the associated application with your application vendor.  2.        Preparation for migration: In case your upgrade activity fails due to some reason and there is no solution possible other than migration,...
Post a Comment
Read more

Data Compression - Row & Page ( Sketch notes)

Just some notes on compression... something helpful for a beginner.
Post a Comment
Read more

Best Practices to be followed for SQL Server

1. Memory Capping SQL Server is an application that uses as much memory as is provided to it. Setting memory too high can cause other applications to compete with SQL on the OS and setting memory too low can cause serious problems like memory pressure and performance issues. It is important to provide at least 4 GB memory for OS use, if you are running only SQL Server as an application on the server.  2. Data File Locations SQL Server accesses data and log files with very different I/O patterns. Data file access is mostly random whilst transaction log file access is sequential. Spinning disk storage requires re-positioning of the disk head for random read and write access. Sequential data is therefore more efficient than random data access. Separating files that have different access patterns helps to minimize disk head movements, and thus optimizes storage performance.  3. Auto growth Auto-growth of the database files should be set in MBs as it will al...
Post a Comment
Read more