Video Screencast Help

SQL Backups

Created: 20 Nov 2012 • Updated: 05 Feb 2014 | 6 comments
This issue has been solved. See solution.

I have few questions with respect to consistency check before backup - around 4 options are available - physical media, full checkup excluding indexes, full checkup including indexes, none.

Question 1 What will be the difference between them.

Question 2 I tired selecting the Adventure database while backing up, only the adventures with the backup option as full checkup excluding indexes. But when i restore the database it lists the adventure database, along with master, model, msdb, report server, pls find the attachemnt.Does it is related to indexes.

Question 3 How can i verify the index options in backup

Experts request you to guide me in the options of BE 2012.

Comments 6 CommentsJump to latest comment

pkh's picture

1) As the names imply, these are checks to ensure that there is no problem with the database before or after it is backed up.  The first checks the physical file and the other two check the logical consistency.

2) These extra databases are used by SQL server itself.  You can search the web and read up on their exact usage.

3) To check the consistency of the indices use the consistency check including indexes option.

RahulG's picture

1. When you perform backup of SQl, backup exec performs a consistency check ,on the backgroung it runs DBCC command on the SQL database. Depending upon the option selected.


I guess following are the command executed on SQL ,depending upon the option you select. (reference

1.  Physical Media : DBCC CheckDB ( dbname,PHYSICAL_ONLY)

2.  full checkup excluding indexes : DBCC CHECKDB (’TestDB’, NOINDEX)

3.  full checkup including indexes : DBCC CHECKDB 

4.  None : No Consitency check performed

Abstract from the following article

"A regular DBCC CHECKDB runs a large number of logical consistency checks, taking up a lot of CPU (and basically being a CPU-bound operation). Using the WITH PHYSICAL_ONLY option limits the checks to running the very fast DBCC CHECKALLOC allocation bitmap consistency checks, and then reading and auditing every allocated page in the database, forcing any page checksums present on the pages to be tested. This turns DBCC CHECKDB into an I/O-bound operation, with a much shorter run time (in fact, it's sometimes even a magnitude or more faster than a full DBCC CHECKDB, which can translate to pretty significant time savings)."

2. No it is not related to indexes. Those are system database.

3. Select the option Full check, including indexes in backup job .as suggested by PKH . You can check the job log to see which option was selected in the backup.

Vengada's picture

I have a query with respect to SQL backup.

I am choosing incremental method for microsoft SQL as Log. I am taking the backup of the entire database.

While restoring what will happen? Since its a log. Any idea or suggestion.

RahulG's picture

You can always restore a database by applying T-LOG backups in sequence. So you need to restore the full backup and all the transaction log backup and when restoring the last t-log backup you need to select the option to commit to database.

Vengada's picture

I tired with both the ways incremental mothod for SQL as both log and differential. However i wasn't able to find any difference. Let me explain you clearly the steps i followed so that u will able to get clear picture.

1 On Sql Server i have created the new database Adventureworks.

2 On BE 2012, i created the Backup Disk to Disk With schedule for Full as Run now with no recuring schedule, and for incremental by specfying the timing Hours Every 1 minute

3 After that i have selected the storage disk

4 In Advanced open file options i have disabled the snapshot technology

5 In Microsoft SQL Option i selected the following option

For Full - Consistency check before backup - physical media

For incremental method for microsoft SQL as log . Ran the job with the selected database on SQL Side.

Full Backup was fine and success.

After that added the row to the database. Then manually ran the incremental job, and i can see the change in size of the database for incremental.

Then went to SQL server, deleted the entire database Adventure.

Tired restoring only the specific table alone by selecting the incremental but it was failed.

Then tired with the full restore, i can find the newly added row was also there for Incremental method as differential.

tired the same way for Incremental method as log also, i can find the database backed up only.

So only asked what is the difference between them with respect to output.

Also correct me if something is wrong.

Thanks Experts

RahulG's picture

What option did you selected when you were restoring the incremental backup .

In a normal senario where you running Full -Differential - and log backup .

Suppose you have 2 differentail backup and 2 incremental backup whichc you run after differentail backup  then you would need to restore the Full + last Differential + 2 log backupsLog backup , when you restore the last log backup you need to commit the logs to the database .