BACKUPs Available in SQL Server

Question

Hi, Ques Explain different types of BACKUPs available in SQL Server. Given a particular scenario, how would you go about choosing a backup plan ?

Senthilkumar · Answer

Hi Arun, SQL Server provides four different methods for backing up your database: A full backup makes a complete backup of your database. You will almost always need to start your backup strategy with a full backup of your database. A file backup is useful when your database is so large that a full backup would take too long. A transaction log backup creates a copy of all changes made to the database that are currently stored in the transaction log. A differential backup stores all changes that have occurred to the database since the last full backup. For more info: http://www.devx.com/getHelpOn/10MinuteSolution/16507

Satyapriya Nayak · Answer

Hi Arun, Types of backups available in SQL Server: Complete: This creates a complete stand alone image of the database. This backup is self dependent and can be restored to either the same or a new database on same or other server. Differential: This backs up only the modified contents since the last backup. They do not provide much flexibility. Transaction log: This backs up all transaction logs since the previous transaction log backup or the complete transaction log backup if there has not been one in past. Files and Filegroups backup: This option is suitable when time constraints are high and one cannot afford to perform a complete database backup. It also needs transaction logs backup to take place to make it worth choosing this option. After restoring file backup, apply transaction logs to roll the file contents forward to make it consistent with the database.. Thanks

BACKUPs Available in SQL Server

Answers (2)