Skip to main content

SQL Server Backups

1. Full Backup:

This is the most common and simplest back up method, It contains all the data in a specific database and enough log that is required to recover the database. It is the base of both differential back up and transaction log back up.

2. Differential Backup:

It depend on the latest full backup of data. It contains all the changes that have been made since the last full backup of data. 

3. Transaction Log Backup:

The transaction log is a record of  all the transactions that have been performed against the database since the last transaction log backup. That means it includes all log records that were not backed up in the last transaction log. With transaction log backup you can recover the database to a specific point of time. This SQL Server backup type is possible only with full or bulk-logged recovery model.

4. File and File group Backup:

This backup type allows you to backup one or more database file or file groups. 

5.Partial Backup:

This type of backup is similar to full database backup, but it does not contain all the file groups.This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files.  This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.
Labels:

Comments

Post a Comment

Popular posts from this blog

Interview Questions and Answers on SQL Server View

    W hat is a View?        Views are virtual tables that hold data from one or more tables. It is a query stored in the database as object. A view does not contain any data itself, it is a set of queries that are applied to one or more tables that are stored within the database as an object   What is the use of view? It allows you to reuse code without  having to write the same complex SQL code over and over. Views are used for security purposes in databases. Views restrict the user from viewing certain columns and rows. In other words, using a view we can apply the restriction on accessing specific rows and columns for a specific user  Can we use ORDER BY clause inside a view?   The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is specified. Can we update a view?       If the view contains joins between mult...
Post a Comment
Read more

SQL Server Triggers

 A trigger is a spacial type of stored procedure which is created on a particular table and it gets executed  in response to certain events (insert, update, delete) on that table. It is a database object which is bound to a table. You can not invoke it explicitly. SQL Server engine executes it implicitly when an event occurs on that table. In SQL server there are two types of triggers. 1. After Trigger (For Trigger) 2. Instead of Trigger After Trigger After trigger is also called as For trigger. It gets fired after an insert or delete or update on a table. Example: After a new employee record is inserted in employee table you want to insert a record in your Audit table saying "A new employee with name XYZ is added at 8 Aug 2017 5:17 PM". Before demonstrating this, let me explain about Magic tables. Magic tables in SQL Server: -Magic tables are temporary tables created by SQL server internally every time when a DML action is performed on a table wi...
Post a Comment
Read more