Does SMSQL run dbcc checkdb on system databases?

[arndt]

I would assume that we do perform a verification on system databases, but I don't see any evidence of this happening when I look at the SMSQL backup reports.  It is logged very clearly when we are performing a dbcc checkdb on user databases.  Any pointers appreciated...thanks!

Mike

[curtish]

I just ran a backup of the system databases (master, model, and msdb).  One of the check boxes on the backup is to verify the databases.  Somehow, they get verified, but I am not sure if it is thru DBCC......

Here is the output from the backup report:

____________________________________________

[16:36:47.348]  *** SnapManager for SQL Server Report

[16:36:47.459]  Backup Time Stamp: 09-14-2011_16.36.27

[16:36:47.535]  Getting SQL Server Database Information, please wait...

[16:36:47.571]  Getting virtual disks information...

[16:36:53.379]  Querying SQL Server instances installed...

[16:36:54.026]  Querying installed SQL Server instances on [WIN2008]...

[16:36:54.034]  Allocating ODBC environment handle...

[16:36:54.037]  Setting environment attribute...

[16:36:54.038]  Setting SQLBrowseConnect option...

[16:36:54.056]  SQLBrowseConnect...

[16:36:54.599]  Parsing SQLBrowseConnect server string...

[16:37:01.418]  Creating threads for enumerating databases of different SQL Server instances.

[16:37:01.428]  Waiting for the threads to finish enumerating databases.

[16:37:01.458]  Connecting to SQL Server instance - WIN2008...

[16:37:01.578]  Getting SQL Server version information...

[16:37:03.131]  Querying database information...

[16:37:08.544]  All threads finished enumerating databases.

[16:37:08.546]  Validating database configuration...

[16:37:08.561]  Setting database disk configuration...

[16:37:08.564]  Setting database locating on SQLRoot path...

[16:37:08.594]  Querying database disk volume configuration...

[16:37:08.595]  Getting SnapInfo directories configuration...

[16:37:08.621]  Checking dataset availability, connecting to SnapDrive...

[16:37:12.715]  Dataset feature is currently unavailable.

[16:37:12.742]  Getting SnapManager dataset info from...

[16:37:12.789]  Dataset info file has not yet created.

[16:37:13.251]  Getting SnapMirror Destinations Volumes...

[16:37:15.719]  Update selected destination volumes information for SQL Server WIN2008 succeeded.

[16:37:15.801]  Get all destination volumes state successfully.

[16:37:19.230]  Full database backup

[16:37:19.245]  Maximum databases of concurrent backup: 255

[16:37:19.256]  Naming convention: Generic

[16:37:19.260]  Run transaction log backup after full database backup: Yes

[16:37:19.261]  Transaction logs will be truncated after backup: Yes

[16:37:19.268]  Archive backup to SnapVault secondary: No

[16:37:19.271]  Keep the oldest 7 full backups.

[16:37:19.272]  Retain up-to-the-minute restore ability for older backups in other management groups: Yes

[16:37:19.274]  Verify database after backup (run DBCC) on WIN2008B: Yes

[16:37:19.275]  Verify on selected SnapMirror destination volumes: No

[16:37:19.283]  Leave database attached after DBCC: No

[16:37:19.284]  Run DBCC CHECKDB with Live Database Before Backup: No

[16:37:19.285]  Run DBCC CHECKDB with Live Database After Backup: No

[16:37:19.286]  Backup Management Group: Standard

[16:37:19.286]  Update SnapMirror after operation: No

[16:37:19.299]  Run Command before the operation: No

[16:37:19.299]  Run Command after the operation: No

[16:37:19.301] 

[16:37:19.312]  Backup Database 1: WIN2008 : master

[16:37:19.316]  Backup Database 2: WIN2008 : model

[16:37:19.319]  Backup Database 3: WIN2008 : msdb

[16:37:19.341]  Backup database list:

[16:37:19.346]  1 - WIN2008 : master

[16:37:19.354]  2 - WIN2008 : model

[16:37:19.355]  3 - WIN2008 : msdb

[16:37:19.683]  Getting SnapInfo directory info...

[16:37:19.684]  1 - Database [master] of [WIN2008] SnapInfo directory located at:

[16:37:19.685]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__master

[16:37:19.699]  2 - Database [model] of [WIN2008] SnapInfo directory located at:

[16:37:19.704]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__model

[16:37:19.705]  3 - Database [msdb] of [WIN2008] SnapInfo directory located at:

[16:37:19.707]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__msdb

[16:37:19.748]  CHECK SNAPMANAGER LICENSE ON LUN.

[16:37:19.781]  Querying LUN license: I...

[16:37:20.118]  Querying LUN license: S...

[16:37:20.180]  *** FULL DATABASE BACKUP

[16:37:20.184]  FULL DATABASE BACKUP [#1]

[16:37:20.202]  Full database backup location for database [master]:

[16:37:20.203]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__master\FG__\StreamFullBackup\09-14-2011_16.36.27__master.FBK

[16:37:20.204]  Connecting to SQL Server WIN2008...

[16:37:20.413]  Starting full database backup for database [master]...

[16:37:21.602]  Database [WIN2008:master] Percent: 6 percent processed.6

[16:37:21.746]  Database [WIN2008:master] Percent: 10 percent processed.10

[16:37:21.754]  Database [WIN2008:master] Percent: 15 percent processed.15

[16:37:21.767]  Database [WIN2008:master] Percent: 21 percent processed.21

[16:37:21.784]  Database [WIN2008:master] Percent: 25 percent processed.25

[16:37:21.785]  Database [WIN2008:master] Percent: 30 percent processed.30

[16:37:21.794]  Database [WIN2008:master] Percent: 36 percent processed.36

[16:37:21.795]  Database [WIN2008:master] Percent: 40 percent processed.40

[16:37:21.796]  Database [WIN2008:master] Percent: 45 percent processed.45

[16:37:21.797]  Database [WIN2008:master] Percent: 51 percent processed.51

[16:37:21.798]  Database [WIN2008:master] Percent: 56 percent processed.56

[16:37:21.798]  Database [WIN2008:master] Percent: 60 percent processed.60

[16:37:21.825]  Database [WIN2008:master] Percent: 66 percent processed.66

[16:37:21.830]  Database [WIN2008:master] Percent: 71 percent processed.71

[16:37:21.830]  Database [WIN2008:master] Percent: 75 percent processed.75

[16:37:21.870]  Database [WIN2008:master] Percent: 81 percent processed.81

[16:37:21.884]  Database [WIN2008:master] Percent: 86 percent processed.86

[16:37:21.918]  Database [WIN2008:master] Percent: 90 percent processed.90

[16:37:21.919]  Database [WIN2008:master] Percent: 97 percent processed.97

[16:37:21.959]  Database [WIN2008:master] Percent: 100 percent processed.100

[16:37:23.280]  Database [WIN2008:master] BACKUP DATABASE successfully processed 371 pages in 0.436 seconds (6.647 MB/sec).

[16:37:23.282]  Full database backup of database [master] completed.

[16:37:23.305]  Verifying full database backup...

[16:37:23.690]  Full database backup verification of database [master] successfully completed.

[16:37:23.694]  FULL DATABASE BACKUP [#2]

[16:37:23.695]  Full database backup location for database [model]:

[16:37:23.696]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__model\FG__\StreamFullBackup\09-14-2011_16.36.27__model.FBK

[16:37:23.713]  Starting full database backup for database [model]...

[16:37:23.918]  Database [WIN2008:model] Percent: 9 percent processed.9

[16:37:23.972]  Database [WIN2008:model] Percent: 14 percent processed.14

[16:37:24.008]  Database [WIN2008:model] Percent: 19 percent processed.19

[16:37:24.009]  Database [WIN2008:model] Percent: 24 percent processed.24

[16:37:24.012]  Database [WIN2008:model] Percent: 29 percent processed.29

[16:37:24.015]  Database [WIN2008:model] Percent: 34 percent processed.34

[16:37:24.036]  Database [WIN2008:model] Percent: 39 percent processed.39

[16:37:24.040]  Database [WIN2008:model] Percent: 44 percent processed.44

[16:37:24.070]  Database [WIN2008:model] Percent: 49 percent processed.49

[16:37:24.072]  Database [WIN2008:model] Percent: 54 percent processed.54

[16:37:24.073]  Database [WIN2008:model] Percent: 59 percent processed.59

[16:37:24.102]  Database [WIN2008:model] Percent: 64 percent processed.64

[16:37:24.102]  Database [WIN2008:model] Percent: 69 percent processed.69

[16:37:24.103]  Database [WIN2008:model] Percent: 74 percent processed.74

[16:37:24.104]  Database [WIN2008:model] Percent: 79 percent processed.79

[16:37:24.105]  Database [WIN2008:model] Percent: 84 percent processed.84

[16:37:24.105]  Database [WIN2008:model] Percent: 89 percent processed.89

[16:37:24.106]  Database [WIN2008:model] Percent: 94 percent processed.94

[16:37:24.107]  Database [WIN2008:model] Percent: 99 percent processed.99

[16:37:24.224]  Database [WIN2008:model] Percent: 100 percent processed.100

[16:37:24.226]  Database [WIN2008:model] BACKUP DATABASE successfully processed 162 pages in 0.264 seconds (4.779 MB/sec).

[16:37:24.226]  Full database backup of database [model] completed.

[16:37:24.227]  Verifying full database backup...

[16:37:24.436]  Full database backup verification of database [model] successfully completed.

[16:37:24.442]  FULL DATABASE BACKUP [#3]

[16:37:24.445]  Full database backup location for database [msdb]:

[16:37:24.449]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__msdb\FG__\StreamFullBackup\09-14-2011_16.36.27__msdb.FBK

[16:37:24.454]  Starting full database backup for database [msdb]...

[16:37:26.128]  Database [WIN2008:msdb] Percent: 8 percent processed.8

[16:37:26.456]  Database [WIN2008:msdb] Percent: 16 percent processed.16

[16:37:26.856]  Database [WIN2008:msdb] Percent: 24 percent processed.24

[16:37:27.412]  Database [WIN2008:msdb] Percent: 33 percent processed.33

[16:37:27.881]  Database [WIN2008:msdb] Percent: 41 percent processed.41

[16:37:27.914]  Database [WIN2008:msdb] Percent: 49 percent processed.49

[16:37:27.936]  Database [WIN2008:msdb] Percent: 57 percent processed.57

[16:37:27.964]  Database [WIN2008:msdb] Percent: 66 percent processed.66

[16:37:27.981]  Database [WIN2008:msdb] Percent: 74 percent processed.74

[16:37:27.997]  Database [WIN2008:msdb] Percent: 82 percent processed.82

[16:37:28.014]  Database [WIN2008:msdb] Percent: 90 percent processed.90

[16:37:28.054]  Database [WIN2008:msdb] Percent: 99 percent processed.99

[16:37:28.198]  Database [WIN2008:msdb] Percent: 100 percent processed.100

[16:37:28.239]  Database [WIN2008:msdb] BACKUP DATABASE successfully processed 1549 pages in 3.485 seconds (3.472 MB/sec).

[16:37:28.243]  Full database backup of database [msdb] completed.

[16:37:28.246]  Verifying full database backup...

[16:37:28.449]  Full database backup verification of database [msdb] successfully completed.

[16:37:28.512]  *** TRANSACTION LOG BACKUP AFTER FULL DATABASE BACKUP

[16:37:28.515]  Transaction log backup for database [master] skipped (Simple Recovery Model).

[16:37:28.518]  Transaction log backup for database [msdb] skipped (Simple Recovery Model).

[16:37:28.519]  TRANSACTION LOG BACKUP AFTER FULL BACKUP [#1]

[16:37:28.520]  Transaction log backup location for database [model]:

[16:37:28.521]  I:\SMSQL_SnapInfo\SQL__WIN2008\DB__model\LogBackup\09-14-2011_16.36.27__model.TRB

[16:37:28.530]  Connecting to SQL server WIN2008...

[16:37:28.629]  Checking recovery model...

[16:37:28.692]  Database [model] recovery model is Full.

[16:37:28.720]  Transaction log backup for database [model] will truncate logs...

[16:37:28.730]  Starting to backup transaction log for database [model]...

[16:37:28.867]  Database [WIN2008:model] Percent: 47 percent processed.47

[16:37:28.901]  Database [WIN2008:model] Percent: 94 percent processed.94

[16:37:28.907]  Database [WIN2008:model] Percent: 100 percent processed.100

[16:37:28.963]  Database [WIN2008:model] BACKUP LOG successfully processed 17 pages in 0.028 seconds (4.743 MB/sec).

[16:37:28.968]  Transaction log backup of database [model] completed.

[16:37:28.983]  Verifying transaction log backup...

[16:37:29.100]  Transaction log backup verification of database [model] successfully completed.

[16:37:29.109] 

[16:37:29.112]  *** BACKUP SNAPINFO DISK

[16:37:29.260] 

[16:37:29.267]  UPDATE MOST RECENT SNAPINFO DRIVE SNAPSHOT NAME

[16:37:29.727]  SNAPSHOT SNAPINFO DRIVE

[16:37:29.742]  Creating Snapshot of LUN(s):

[16:37:29.743]  LUN [I]

[16:37:29.747]  Snapshot name: sqlinfo__win2008__recent

[16:37:32.479]  Snapshot SnapInfo drive(s) successfully completed.

[16:37:32.689]  *** DELETE THE OLDEST STREAM BASED BACKUP DATA SETS

[16:37:32.708] 

[16:37:32.709]  *** DELETING PREVIOUS BACKUPS: master

[16:37:32.710]  Querying stream based full database backup for [master]...

[16:37:33.149]  There are only [3] full database backup sets for master. 7 backups are selected to retain.

[16:37:33.151]  No full database backup of [master] will be deleted.

[16:37:33.151] 

[16:37:33.152]  *** DELETING PREVIOUS BACKUPS: model

[16:37:33.152]  Querying stream based full database backup for [model]...

[16:37:33.159]  There are only [3] full database backup sets for model. 7 backups are selected to retain.

[16:37:33.160]  No full database backup of [model] will be deleted.

[16:37:33.160] 

[16:37:33.173]  *** DELETING PREVIOUS BACKUPS: msdb

[16:37:33.178]  Querying stream based full database backup for [msdb]...

[16:37:33.180]  There are only [3] full database backup sets for msdb. 7 backups are selected to retain.

[16:37:33.181]  No full database backup of [msdb] will be deleted.

[16:37:34.084] 

[16:37:34.085]  *** DELETING LOG SNAPINFO DISK SNAPSHOT

[16:37:34.093]  Get log snapshot list from LUN [I]: ...

[16:37:34.502]  There are only 1 snapinfo snapshots left for this database backup set.

[16:37:34.504]  No snapinfo snapshots will be deleted.

[16:37:34.787] 

[16:37:34.789]  **** FULL DATABASE BACKUP RESULT SUMMARY ****

[16:37:34.789]  Backup Time: 09-14-2011_16.36.27

[16:37:34.790]  Backup Group [#1]:

[16:37:34.792]  #1 : [WIN2008 - master] : OK

[16:37:34.793]  #2 : [WIN2008 - model] : OK

[16:37:34.793]  #3 : [WIN2008 - msdb] : OK

[16:37:34.808] 

[16:37:34.820]  *** SNAPMANAGER BACKUP JOB ENDED AT: [09-14-2011 16.37.34]

[16:37:34.831]  Full database backup with verification completed successfully

[abhisek]

Hi Mike ,

By default, a SnapManager full database backup operation runs DBCC CHECKDB immediately after the backup is complete. Yes, if System DB included in the backupset it is also part of the verification process.

I am not sure why you are not observing any log on DBCC operation on SystemDB’s. You should see it in the backup report.

Eg:

========

LIVE DATABASE DBCC CHECKDB -

Connecting to SQL server SQL_VM1...

DBCC CHECKDB (N'master') WITH NO_INFOMSGS, PHYSICAL_ONLY

DBCC CHECKDB completed successfully.

LIVE DATABASE DBCC CHECKDB -

DBCC CHECKDB (N'model') WITH NO_INFOMSGS, PHYSICAL_ONLY

DBCC CHECKDB completed successfully.

LIVE DATABASE DBCC CHECKDB -

DBCC CHECKDB (N'msdb') WITH NO_INFOMSGS, PHYSICAL_ONLY

DBCC CHECKDB completed successfully.

Regards,

Abhishek

[qzhang · Accepted solution]

Since system db is backed up using stream method, SMSQL doesn't run DBCC checkdb on system db backup.

However, you have option to run DBCC checkdb on system db before and after you migrate system db while using config wizard.

Thanks,

-Qing

[schoolofish]

I just inherited SQL with AlwaysOn,  and the NetApp, etc.

How do I verify that Net SMSQL is even running a stream backup?   The SMSQL - system backup jobs that are in SSMS fail every night with error code "Invalid Input" and my boss said don't worry about the sched job b/c the snapshot stream backup is catching it.    How do I know that?  I suppose I could mount a past restore point and check things out?

Thanks so much for any input.

David

[sam_wozniak]

So to further clarify, would it be safe to say that SMSQL only runs verification on databases backed up via a snapshot because it will need to mount the snapshot for the verification?  Today I was trying to figure out why after taking an unverified backup of a user database (along with all the system databases), through the restore view that the user database showed that it wasn't verified (as expected), but the system databases DID show that they were verified.